I want to do a select that do a cast only for a specific ID but it doesn't seems to work.
Example :
SELECT
CASE
WHEN(@ID <> 1) THEN Code
WHEN(@ID = 1) THEN Cast(Code AS int)
END Code FROM ....
Any Idea ?
views:
174answers:
3
+1
A:
It works for me. Check if the @id is of type int and if all values of column Code can be casted to int.
UPDATE If you have a value that can't be casted to int, your query won't work.
So you can write 2 different queries.
Smth like
IF @id = 1 THEN
SELECT code ...
ELSE
SELECT Cast(Code AS int) as Code
hgulyan
2010-05-13 12:35:28
That's the name of the output column. There should be an `AS` before it.
Marcelo Cantos
2010-05-13 12:36:49
It's ok. my mistake. It works without AS.
hgulyan
2010-05-13 12:40:21
@Marcelo the `as` is not compulsory.
Blorgbeard
2010-05-13 12:40:30
I still get the error : Conversion failed when converting the nvarchar value 'ASS2902' to data type int even if @ID = 2 but I just want to do the cast for @ID = 1
Mathieu
2010-05-13 12:44:11
If 'Code' is already of type 'int', then there is no point to cast it to 'int'. If 'Code' is not of type 'int', then it won't work to cast it to 'int' only in certain circumstances. The overall 'CASE' expression has to have a single type.
Daniel Pratt
2010-05-13 12:46:04
The cast Work for every 'Code' when @ID = 1, in fact my stored procs works when @ID = 1 but failed when it's another @ID but I don't understand why the cast occurs when @ID <> 1
Mathieu
2010-05-13 12:50:31
@Mathieu, it gets two values before evaluating no matter of id
hgulyan
2010-05-13 12:54:49
@hgulyan Thanks it works, really appreciate ! Sorry I cannot vote up cause I'm new on stackoverflow
Mathieu
2010-05-13 14:07:43
you welcome, Mathieu, and good luck:)
hgulyan
2010-05-13 18:29:17
@Daniel Pratt, some types can be casted to int, by the way. for example, float values (not all surely)
hgulyan
2010-05-13 21:00:57
+2
A:
Why do want to do this? A SQL Server expression has a single fixed type. In other words, a single expression can't be varchar(50) or int depending on how the expression is evaluated. You could cast each case to sql_variant, but that may or may not make sense based on what you're trying to do.
EDIT
If you are executing this query from a stored procedure, you could create an IF..ELSE block to execute a different version of the query based on the value of @ID. For example:
IF (@ID = 1) BEGIN
SELECT Cast(Code AS int) AS Code FROM ...
END
ELSE BEGIN
SELECT Code FROM ...
END
Daniel Pratt
2010-05-13 12:43:19
+1, for a given `CASE`, all of its `WHEN` s and the `ELSE` must return the same data type.
KM
2010-05-13 12:49:53
It's because the 'Employee code' is a nvarchar in the BD but one of my user use number as 'Employee code' but they are store are nvarchar and now he would like to see his employee order by 'Employee code' but when you sort by 'Employee code' it's an alphabetical order.So for this precise user I want to order numerically.Maybe there is a better solution ....
Mathieu
2010-05-13 12:55:31
@Daniel Pratt Thanks it works, really appreciate ! Sorry I cannot vote up cause I'm new on stackoverflow
Mathieu
2010-05-13 14:09:07
A:
You could have also written:
select case when @ID = 1 then CAST(Code as int) else Code end as Code
from...
By the way, any data containing alphabetic characters won't cast to int.
Perhaps could we better help you if you tell us what you want to achieve, with some sample data provided?
Will Marcouiller
2010-05-13 12:50:58
As I mentioned in a prior comment, the whole CAST expression must have a single type. Because the first alternative of the CAST expression here has type 'int', SQL Server will implicitly convert all subsequent alternatives to int. Even when @ID is not 1, all values of 'Code' will be converted to 'int' and any values of 'Code' that cannot be converted to 'int' will cause an error.
Daniel Pratt
2010-05-13 13:57:37