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?
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.
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.
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.
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.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With