Have Geek Brain

SQL Server, select CASE with different cast

I want to make a selection that only broadcasts to a specific ID, but it doesn't work.

Example:

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

Any idea?

+2


a source to share


3 answers


This works for me. Check if @id is int and if all values ​​in Code column can be cast to int .

UPDATE If you have a value that cannot be executed for an int, your query will not work.

So, you can write 2 different queries.



Smth like

IF @id = 1 THEN
 SELECT code ...
ELSE 
 SELECT Cast(Code AS int) as Code
+1


a source


Why do this? A SQL Server expression has one fixed type. In other words, one expression cannot be varchar(50)

or int

depending on how the expression is evaluated. You can apply each case to sql_variant

, but it may or may not make sense depending on what you are trying to do.

EDIT



If you are executing this query from a stored procedure, you can create an IF..ELSE block to execute a different version of the query based on the @ID value. For instance:

IF (@ID = 1) BEGIN
    SELECT Cast(Code AS int) AS Code FROM ...
END
ELSE BEGIN
    SELECT Code FROM ...
END
+2


a source


You could also write:

select case when @ID = 1 then CAST(Code as int) else Code end as Code
    from...

By the way, any data containing alphabetic characters will not be cast to int.

Perhaps we better help you if you tell us what you want to achieve with the provided sample data?

0


a source






More articles:


All Articles