如何正确使用CASE语句？存储过程UPDATE附近语法错误排查

SQL里的CASE是表达式，只能用来返回单个值，没法用来包裹执行UPDATE这类语句块，这就是你收到语法错误提示的原因。

方式一：用IF...ELSE（推荐，逻辑清晰无风险） #

ALTER PROCEDURE [dbo].[UpdateRFQStatusDashboardCountForAllUsers]
(
    @UserID [nvarchar](128)='6aab7553-72ef-46d8-b831-5c8d5e269cb3',
    @ColumnName  [nvarchar](50)='Close'
)
AS
BEGIN
    IF @ColumnName = 'New'
    BEGIN
        UPDATE [dbo].[RFQStatusDashboardCountForAllUsers]
        SET New = New + 1
        WHERE [UserID] = @UserID
    END
    ELSE IF @ColumnName = 'Close'
    BEGIN
        UPDATE [dbo].[RFQStatusDashboardCountForAllUsers]
        SET [Open] = [Open] - 1, Closed = Closed + 1
        WHERE [UserID] = @UserID
    END
END

方式二：动态SQL（仅当分支极多时考虑，注意防注入） #

如果你坚持要类似CASE的分支写法，可以用动态SQL，但必须参数化避免注入：

ALTER PROCEDURE [dbo].[UpdateRFQStatusDashboardCountForAllUsers]
(
    @UserID [nvarchar](128)='6aab7553-72ef-46d8-b831-5c8d5e269cb3',
    @ColumnName  [nvarchar](50)='Close'
)
AS
BEGIN
    DECLARE @SqlStmt NVARCHAR(MAX)
    
    SET @SqlStmt = CASE @ColumnName
        WHEN 'New' THEN 
            N'UPDATE [dbo].[RFQStatusDashboardCountForAllUsers] SET New = New + 1 WHERE [UserID] = @UserID'
        WHEN 'Close' THEN 
            N'UPDATE [dbo].[RFQStatusDashboardCountForAllUsers] SET [Open] = [Open] - 1, Closed = Closed + 1 WHERE [UserID] = @UserID'
    END
    
    IF @SqlStmt IS NOT NULL
        EXEC sp_executesql @SqlStmt, N'@UserID NVARCHAR(128)', @UserID = @UserID
END

别执着于用CASE替代IF...ELSE——在需要执行不同语句块的场景下，IF...ELSE的可读性和维护性都更好，也不会有动态SQL的注入风险。只有当分支数量极多、需要统一管理SQL模板时，动态SQL才值得考虑。

内容的提问来源于stack exchange，提问作者Ntando