SQL Server 中的多个独立 IF 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20413064/
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
Multiple separate IF conditions in SQL Server
提问by Hyman Thor
I have multiple IFstatements that are independent of each other in my stored procedure. But for some reason they are being nested inside each other as if they are part of one big if statement
IF我的存储过程中有多个相互独立的语句。但出于某种原因，它们相互嵌套，就好像它们是一个大 if 语句的一部分
ELSE IF(SOMETHNGZ)
BEGIN
IF(SOMETHINGY)
BEGIN..END
ELSE IF (SOMETHINGY)
BEGIN..END
ELSE
BEGIN..END
--The above works I then insert this below and these if statement become nested----
IF(@A!= @SA)
IF(@S!= @SS)
IF(@C!= @SC)
IF(@W!= @SW)
--Inserted if statement stop here
END
ELSE <-- final else
So it will be treated like this
所以会被这样处理
IF(@A!= @SA){
IF(@S!= @SS){
IF(@C!= @SC) {
IF(@W!= @SW){}
}
}
}
What I expect is this
我期待的是这个
IF(@A!= @SA){}
IF(@S!= @SS){}
IF(@C!= @SC){}
IF(@W!= @SW){}
I have also tried this and it throws Incorrect syntax near "ELSE". Expecting "CONVERSATION"
我也试过这个，它抛出 Incorrect syntax near "ELSE". Expecting "CONVERSATION"
IF(@A!= @SA)
BEGIN..END
IF(@S!= @SS)
BEGIN..END
IF(@C!= @SC)
BEGIN..END
IF(@W!= @SW)
BEGIN..END
Note that from ELSE <--final elsedown is now nested inside IF(@W!= @SW)Even though it is part of the outer if statement ELSE IF(SOMETHNGZ)before.
请注意， from ELSE <--final elsedown 现在嵌套在内部， IF(@W!= @SW)尽管它之前是外部 if 语句的一部分ELSE IF(SOMETHNGZ)。
EDIT
编辑
As per request my full statement
根据要求，我的完整声明
ALTER Procedure [dbo].[SP_PLaces]
@ID int,
..more params
AS
BEGIN
SET NOCOUNT ON
DECLARE @SomeId INT
..more varaible
SET @SomeId = user define function()
..more SETS
IF(@ID IS NULL)
BEGIN
BEGIN TRY
INSERT INTO Places
VAlUES(..Values...)
... more stuff...
BEGIN TRY
exec Store procedure
@FIELD = 15, ... more params...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
RETURN 0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
END
ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places))
BEGIN
SELECT @MyName = Name ...
...Some stuff....
IF(SOMETHNG_1)
BEGIN TRY
UPDATE ....
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
ELSE IF(SOMETHNG_2)
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
ELSE
BEGIN
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
END
--The above works I then insert this below and these if statement become nested----
IF(@A!= @SA)
BEGIN
exec Stored procedure
@FIELD = 15,
... more params...
END
IF(@S!= @SS)
BEGIN
exec Stored procedure
@FIELD = 10,
... more params...
END
IF(@C!= @SC)
BEGIN
exec Stored procedure
@FIELD = 17,
... more params...
END
IF(@W!= @SW)
BEGIN
exec Stored procedure
@FIELD = 12,
... more params...
END
--Inserted if statement stop here
END
ELSE
BEGIN
SET @ResultMessage = 'Update/Delete Failed. No record found with ID:'+CONVERT(varchar(50), @ID)
SELECT @ResultMessage AS 'Message'
RETURN -1
END
Set NOCOUNT OFF
END
回答by M.Ali
IF you are checking one variable against multiple condition then you would use something like this Here the block of code where the condition is true will be executed and other blocks will be ignored.
如果您正在根据多个条件检查一个变量，那么您将使用类似这样的内容 这里将执行条件为真的代码块，而其他块将被忽略。
IF(@Var1 Condition1)
BEGIN
/*Your Code Goes here*/
END
ELSE IF(@Var1 Condition2)
BEGIN
/*Your Code Goes here*/
END
ELSE --<--- Default Task if none of the above is true
BEGIN
/*Your Code Goes here*/
END
If you are checking conditions against multiple variables then you would have to go for multiple IF Statements, Each block of code will be executed independently from other blocks.
如果要针对多个变量检查条件，则必须使用多个 IF 语句，每个代码块都将独立于其他块执行。
IF(@Var1 Condition1)
BEGIN
/*Your Code Goes here*/
END
IF(@Var2 Condition1)
BEGIN
/*Your Code Goes here*/
END
IF(@Var3 Condition1)
BEGIN
/*Your Code Goes here*/
END
After every IF statement if there are more than one statement being executed you MUST put them in BEGIN..END Block. Anyway it is always best practice to use BEGIN..END blocks
在每个 IF 语句之后，如果有多个语句被执行，您必须将它们放在 BEGIN..END 块中。无论如何，使用 BEGIN..END 块始终是最佳实践
Update
更新
Found something in your code some BEGIN END you are missing
在你的代码中找到了一些 BEGIN END 你缺少的东西
ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places)) -- Outer Most Block ELSE IF
BEGIN
SELECT @MyName = Name ...
...Some stuff....
IF(SOMETHNG_1) -- IF
--BEGIN
BEGIN TRY
UPDATE ....
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
-- END
ELSE IF(SOMETHNG_2) -- ELSE IF
-- BEGIN
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
-- END
ELSE -- ELSE
BEGIN
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
END
--The above works I then insert this below and these if statement become nested----
IF(@A!= @SA)
BEGIN
exec Store procedure
@FIELD = 15,
... more params...
END
IF(@S!= @SS)
BEGIN
exec Store procedure
@FIELD = 10,
... more params...
回答by LittleSweetSeas
To avoid syntax errors, be sure to always put BEGINand ENDafter an IFclause, eg:
为了避免语法错误，一定要始终把BEGIN和END一个后IF条款，例如：
IF (@A!= @SA)
BEGIN
--do stuff
END
IF (@C!= @SC)
BEGIN
--do stuff
END
... and so on. This should work as expected. Imagine BEGINand ENDkeyword as the opening and closing bracket, respectively.
... 等等。这应该按预期工作。想象BEGIN和END关键字分别作为开始和结束括号。
回答by dbj
Maybe this is a bit redundant, but no one appeared to have mentioned this as a solution.
也许这有点多余，但似乎没有人提到这是一种解决方案。
As a beginner in SQL I find that when using a BEGINand ENDSSMS usually adds a squiggly line with incorrect syntax near 'END'to END, simply because there's no content in between yet. If you're just setting up BEGINand ENDto get started and add the actual query later, then simply add a bogus PRINTstatement so SSMS stops bothering you.
作为SQL我初学者使用发现，当BEGIN与ENDSSMS通常增加了一个与波浪线incorrect syntax near 'END'要结束了，原因很简单，就是在尚未之间没有内容。如果您只是设置BEGIN并END开始并稍后添加实际查询，那么只需添加一个虚假PRINT语句，这样 SSMS 就不会打扰您了。
For example:
例如：
IF (1=1)
BEGIN
PRINT 'BOGUS'
END
The following will indeed set you on the wrong track, thinking you made a syntax error which in this case just means you still need to add content in between BEGIN and END:
以下确实会让您走上错误的轨道，认为您犯了语法错误，在这种情况下，这仅意味着您仍然需要在 BEGIN 和 END 之间添加内容：
IF (1=1)
BEGIN
END
