Re: Weird Cast operator
From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 09/17/04
- Next message: Gene S.: "Table ID Question"
- Previous message: Hugo Kornelis: "Re: INSERT statement"
- In reply to: Ganesan Rajaraman: "Re: Weird Cast operator"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 17 Sep 2004 22:10:10 +0000 (UTC)
Ganesan Rajaraman (ganesanrajaraman@gmail.com) writes:
> We know that this is not true. Please refer to @outFails sql from my
> first post. If it were to evaluate THEN clause only when WHEN is true,
> why @outFails fails?
Let's go back to your queries:
select @outWorks = CASE
WHEN RTrim(LTrim(@in)) NOT LIKE '%[^0-9]%'
THEN Cast(Cast(@in as int) as varchar)
ELSE @in
END
The datatype of this CASE expresion is varchar. Both branches return
varchar, so that is not strange.
select @outFails = CASE
WHEN RTrim(LTrim(@in)) NOT LIKE '%[^0-9]%'
THEN Cast(@in as int)
ELSE @in
END
Here, the value of the CASE expression is int. This is because the two
bracnhes returns different datatype. Therefore there will be an automatic
conversion to the data-type that has the highest priority of the two
in the SQL datatype precedence list. Varchar has lower precendence than
int, so thus the value is int.
If @in = '003' the value matches the WHEN condition, and thus expression
returns 3. But if @in is '00-3', you end up in the ELSE branch. Problem
though is that '00-3' cannot be converted to int, so the statement
fails.
-- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Gene S.: "Table ID Question"
- Previous message: Hugo Kornelis: "Re: INSERT statement"
- In reply to: Ganesan Rajaraman: "Re: Weird Cast operator"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
