SQL 如何在 if else 语句中设置多个值？

How to set multiple values inside an if else statement?

提问by Mattias

I'm trying to SET more than one value within the if else statement below, If I set one value it works, but if I set two values, it doesn't work:

我正在尝试在下面的 if else 语句中设置多个值，如果我设置一个值它有效，但如果我设置两个值，它不起作用：

DECLARE @test1 varchar(60);
DECLARE @test2 varchar(60);


IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
SET @test1 = 'test1'
SET @test2 = 'test2' 
ELSE
SET @test1 = 'testelse'
SET @test2 = 'testelse'

Error message: "Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ELSE'."

错误消息：“消息 156，级别 15，状态 1，第 9 行
关键字'ELSE'附近的语法不正确。”

However it seems to be possible to have multiple SET variables after the else; this code works:

然而，在 else 之后似乎可以有多个 SET 变量；此代码有效：

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
SET @test1 = 'test1'
ELSE
SET @test1 = 'testelse'
SET @test2 = 'testelse'

How can I do this correctly?

我怎样才能正确地做到这一点？

回答by Abdul Rasheed

If you have more than one statement in a if condition, you must use the BEGIN ... ENDblock to encapsulate them.

如果 if 条件中有多个语句，则必须使用BEGIN ... END块来封装它们。

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
 SET @test1 = 'test1'
 SET @test2 = 'test2' 
END
ELSE
BEGIN
 SET @test1 = 'testelse'
 SET @test2 = 'testelse'
END

回答by MatBailie

Use BEGINand ENDto mark a multi-statement block of code, much like using {and }in other languages, in which you can place your multiple SETstatements...

使用BEGINandEND标记多语句代码块，就像在其他语言中使用{and一样}，您可以在其中放置多个SET语句...

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
    SET @test1 = 'test1'
    SET @test2 = 'test2'
END
ELSE
BEGIN
    SET @test1 = 'testelse'
    SET @test2 = 'testelse'
END

Or, use SELECTto assign values to your variables, allowing both to be assigned in a single statement and so avoid requiring the use of BEGINand END.

或者，用于SELECT为变量赋值，允许在单个语句中同时赋值，从而避免需要使用BEGIN和END。

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
    SELECT
        @test1 = 'test1',
        @test2 = 'test2' 
ELSE
    SELECT
        @test1 = 'testelse',
        @test2 = 'testelse'

回答by HoneyBadger

If you have multiple statements after the IFyou have to use beginand end(similar to accolades in c#, for example).

如果在 之后有多个语句，则IF必须使用beginand end（例如，类似于 c# 中的 accolades）。

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
   SET @test1 = 'test1'
   SET @test2 = 'test2' 
END
ELSE
BEGIN
   SET @test1 = 'testelse'
   SET @test2 = 'testelse'
END

回答by Alexei

The behavior makes sense since your first trial will be split like this:

这种行为是有道理的，因为您的第一次试验将像这样拆分：

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
SET @test1 = 'test1'

SET @test2 = 'test2' 

ELSE
   SET @test1 = 'testelse'

and ELSEwill fail since it does not belong to any IF statement.

并且ELSE会失败，因为它不属于任何 IF 语句。

Consider doing like this:

考虑这样做：

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
   SET @test1 = 'test1'
   SET @test2 = 'test2' 
END
ELSE
BEGIN 
   SET @test1 = 'testelse'
   SET @test2 = 'testelse'
END

回答by t-clausen.dk

The second expression in your question will always result execute @test2 = 'testelse'because the ELSE ends right after the first expression after the else:

您问题中的第二个表达式将始终导致 execute @test2 = 'testelse'因为 ELSE 在 else 之后的第一个表达式之后立即结束：

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
SET @test1 = 'test1'
ELSE
SET @test1 = 'testelse'
-- IF is done evaluating here
SET @test2 = 'testelse' 

If you have multiple expressions within the IF, you can always group the expressions using BEGIN / END.

如果在 IF 中有多个表达式，则始终可以使用 BEGIN / END 对表达式进行分组。

But in your case, the most simple way would be this:

但就您而言，最简单的方法是：

IF (SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10
  SELECT @test1 = 'test1', @test2 = 'test2' 
ELSE
  SELECT @test1 = 'testelse', @test2 = 'testelse'