从 CASE 语句分配给 T-SQL 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6945979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assign to a T-SQL variable from a CASE statement
提问by rlb.usa
I'd like to assign some variables inside a query that uses CASEstatements 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?
将这些变量粘贴在那里的正确位置/方法是什么？
回答by JosephStyons
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 语句中分配给变量。假设整个 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?
你能告诉我们你遇到了什么具体的错误吗？
回答by Abe Miessler
You could probably do this more easily using ISNULLor COALESCE:
您可以使用ISNULLor更轻松地做到这一点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 ...
回答by Mohan Sharma
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)
