SQL Server, Select CASE with different casting

I want to do a select that do a cast only for a specific ID but it doesn't seems to work.

Exa开发者_如何转开发mple :

SELECT
  CASE
    WHEN(@ID <> 1) THEN Code
    WHEN(@ID = 1) THEN Cast(Code AS int)
  END Code FROM ....

Any Idea ?

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

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

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?