我有多个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
所以会这样对待
IF(@A!= @SA){
IF(@S!= @SS){
IF(@C!= @SC) {
IF(@W!= @SW){}
}
}
}
我期望是这样的
IF(@A!= @SA){}
IF(@S!= @SS){}
IF(@C!= @SC){}
IF(@W!= @SW){}
我也试过这个,它在“ELSE”附近抛出不正确的语法.期待“对话”
IF(@A!= @SA) BEGIN..END IF(@S!= @SS) BEGIN..END IF(@C!= @SC) BEGIN..END IF(@W!= @SW) BEGIN..END
注意,从ELSE < - final else down现在嵌套在IF(@W！= @SW)内即使它是外部if语句ELSE IF(SOMETHNGZ)之前的一部分. 编辑 根据要求我的完整声明
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
解决方法
如果你根据多个条件检查一个变量,那么你会使用这样的东西
这里将执行条件为true的代码块,其他块将被忽略.
这里将执行条件为true的代码块,其他块将被忽略.
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
语句,每个代码块将独立于其他块执行.
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
在每个IF语句之后,如果执行了多个语句,则必须将它们放入
BEGIN..END块.无论如何,总是最好的做法是使用BEGIN..END块
更新
在你的代码中找到一些你缺少的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...
