SQL Server 2008 R2 If/else 语法

Question

我正在尝试在SQL Server中编写一个相当简单的存储过程。如果我的错误来源很明显，我就无法捕获它。SSMS给出了"括号附近的语法不正确"的错误提示。任何帮助都将不胜感激。这里是存储过程的代码：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sprocAddSymbols] @symbol NVARCHAR(10)

AS 

BEGIN

DECLARE @symbolCheck NVARCHAR(10)
DECLARE @statusCheck NVARCHAR(10)

SET @symbolCheck = (SELECT Symbol FROM tblSymbolsMain WHERE Symbol = @symbol)
SET @statusCheck = (SELECT SymbolStatus FROM tblSymbolsMain WHERE Symbol = @symbol)

IF (@symbolCheck IS NOT NULL AND @statusCheck = 'Inactive')
BEGIN
    UPDATE tblSymbolsMain
    SET SymbolStatus = 'Active'
    WHERE Symbol = @symbol
END
ELSE
    INSERT INTO tblSymbolsMain (Symbol, DateAdded, Status)
    VALUES (@symbol, GETDATE(), 'Active')

- StatsViaCsh

Answer 1

你缺少结尾的END。始终对代码进行缩进。这不仅看起来更好，而且可以避免这种情况。即使你在IF / ELSE或其他任何不需要BEGIN / END的块中只有一个语句，使用BEGIN和END也是更好的选择。当有人稍后添加语句并认为它是IF或ELSE块的一部分时，它可以减少维护上的麻烦。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sprocAddSymbols] @symbol NVARCHAR(10)

AS 

BEGIN

    DECLARE @symbolCheck NVARCHAR(10)
    DECLARE @statusCheck NVARCHAR(10)

    SET @symbolCheck = (SELECT Symbol FROM tblSymbolsMain WHERE Symbol = @symbol)
    SET @statusCheck = (SELECT SymbolStatus FROM tblSymbolsMain WHERE Symbol = @symbol)

    IF (@symbolCheck IS NOT NULL AND @statusCheck = 'Inactive')
    BEGIN
        UPDATE tblSymbolsMain
        SET SymbolStatus = 'Active'
        WHERE Symbol = @symbol
    END
    ELSE
    BEGIN
        INSERT INTO tblSymbolsMain (Symbol, DateAdded, Status)
        VALUES (@symbol, GETDATE(), 'Active')
    END

END

- amit_g