从CASE语句分配给T-SQL变量

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.

我想在查询中分配一些变量，这些变量对它的列使用CASE语句。不太确定如何做到这一点，无法找到正确的语法。

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?

将这些变量粘贴在那里的正确位置/方法是什么？

3 个解决方案

#1

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:

你给出的例子应该有效。您可以从案例陈述中分配变量。只是假装整个CASE..WHEN..THEN..ELSE..END块是一个字段。这是一个通用的例子：

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?

你能告诉我们你得到的具体错误吗？

#2

You could probably do this more easily using ISNULL or COALESCE:

您可以使用ISNULL或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 ...

#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)

#1