我在存储过程中有多个彼此独立的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”附近错误的语法。期望"CONVERSATION"
IF(@A!= @SA)
BEGIN..END
IF(@S!= @SS)
BEGIN..END
IF(@C!= @SC)
BEGIN..END
IF(@W!= @SW)
BEGIN..END
注意,从ELSE <--final else
开始到结尾的部分现在嵌套在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
sp_
前缀。微软已经将该前缀保留给自己使用(请参阅命名存储过程),而且你可能会在未来某个时候遇到名称冲突的风险。这也会影响你的存储过程性能。最好是避免使用sp_
,使用其他前缀或完全不使用前缀! - marc_s