Assigning a T-SQL variable from a CASE statement

Question

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

This is what I have so far, but it received 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 is the right place / way to insert these variables there?

Answer 1

The example you provided should work. You can assign variables from the case statement. Just pretend that the entire CASE..WHEN..THEN..ELSE..END block is a field. Here is a general 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 errors you get?

Answer 2

You could do this more easily using ISNULL or COALESCE :

 select top (1) @theID = ISNULL(B.ID, A.ID), @theName = ISNULL(B.Name, A.Name), from B left join A on A.ID = B.ID where ... 

Answer 3

 DECLARE @SmallBlindSeatId INT DECLARE @BigBlindSeatId INT DECLARE @DealerSeatId INT DECLARE @NextTurn INT SELECT @DealerSeatId=( CASE WHEN BlindsInfo=1 THEN SeatId ELSE @DealerSeatId END ), @SmallBlindSeatId=( CASE WHEN BlindsInfo=2 THEN SeatId ELSE @SmallBlindSeatId END), @BigBlindSeatId=( CASE WHEN BlindsInfo=3 THEN SeatId ELSE @BigBlindSeatId END), @NextTurn=( CASE WHEN NEXTTURN=1 THEN SeatId ELSE @NextTurn END) FROM ABC WHERE TESTCASEID=1 PRINT(@DealerSeatId) PRINT(@SmallBlindSeatId) PRINT(@BigBlindSeatId) PRINT (@NextTurn)