Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Assign to a T-SQL variable from a CASE statement

I'd like to assign some variables inside a query that uses CASE statements for it's columns. Not quite sure how to do this, having trouble finding the right syntax.

This is what I have so far, but it's got syntax errors.

 -- set @theID and @theName with their appropriate values
 select top (1) 
 @theID = (Case when B.ID IS NULL then A.ID else B.ID END) ,
 @theName = (Case when B.Name IS NULL then A.Name else B.Name END) 
 from B left join A on A.ID = B.ID where ...

What's the correct place/way to stick those variables in there?

like image 440
rlb.usa Avatar asked Aug 04 '11 17:08

rlb.usa


People also ask

How do you assign a value to a variable in a case statement in SQL?

to set the value of a single variable according to a CASE expression. If your real logic is more complicated (e.g. need to set multiple variables inside a condition) look at IF ... ELSE instead. CASE is an expression not a flow of control construct. Show activity on this post.

How do I add a case statement to a SELECT clause in SQL?

SQL Case Statement Syntax After that comes the keyword THEN and the value for that condition, like WHEN <condition> THEN <stuff> . This can then be followed by other WHEN / THEN statements. At the end you can add a value to use by default if none of the conditions are true with the ELSE keyword, as shown below.

Can I use case in FROM clause SQL?

No, you can't pick a table to query using a CASE statement. CASE statements only go within expressions, such as for a column's value or as part of your WHERE expression.

Can we use CASE statement in SELECT query?

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.


1 Answers

The example you've given should work. You can assign to variables from a case statement. Just pretend that the entire CASE..WHEN..THEN..ELSE..END block is a field. Here is a generic example:

declare
  @string1 nvarchar(100) = null
 ,@string2 nvarchar(100) = null
 ;

select top 1
  @string1 = case when 1=1 then 'yes' else 'no' end
 ,@string2 = case when 1=0 then 'yes' else 'no' end

print 'string1 = ' + @string1
print 'string2 = ' + @string2

Gives:

string1 = yes
string2 = no

Can you tell us what specific error(s) you are getting?

like image 129
JosephStyons Avatar answered Sep 23 '22 15:09

JosephStyons