如何解决存储过程中动态SQL的语法错误及变量调用问题

1. 冒号附近语法错误修复 #

错误原因：动态SQL的整段语句本身是用单引号包裹的字符串，你直接在内部使用单引号包裹:，SQL会将内部的单引号识别为字符串的结束符，导致语法断裂，冒号直接暴露在SQL语法中触发报错。
正确转义写法：字符串内部的单引号需要写成两个连续的单引号进行转义，修改后对应代码如下：

[PersonnelNo] + '' : '' + [FirstName] + '' '' + [LastName] AS Result

2. 动态SQL中@KeyWord模糊查询正确实现 #

错误原因：动态SQL的执行上下文和存储过程的外部上下文是隔离的，直接在Exec的字符串中引用外部@KeyWord变量会报变量未定义的错误。推荐使用sp_executesql代替Exec实现参数传递，既可以避免SQL注入风险，也不用额外处理字符串转义。

修正后的完整存储过程代码 #

ALTER PROCEDURE [dbo].[ret_RetiredPersonnel_Search]
@KeyWord nvarchar (32),
@WhereClause varchar(MAX) 
AS
BEGIN
    DECLARE @SQL nvarchar(MAX)
    -- 拼接动态SQL语句，内部单引号统一转义为双单引号
    SET @SQL = N'
    SELECT 
        [Guid],
        [FirstName],
        [LastName],
        [PersonnelNo],
        [PersonnelNo] + '' : '' + [FirstName] + '' '' + [LastName] AS Result
    FROM ret_RetiredPersonnel 
    WHERE 
        (
            [FirstName] LIKE N''%'' + @InnerKeyWord + ''%'' 
            OR [LastName] LIKE N''%'' + @InnerKeyWord + ''%'' 
            OR [PersonnelNo] LIKE N''%'' + @InnerKeyWord + ''%''
        ) ' + ISNULL(@WhereClause, '') + '
    ORDER BY [LastName] DESC, [FirstName] DESC, [PersonnelNo] DESC'

    -- 通过sp_executesql传递参数，避免注入和转义问题
    EXEC sp_executesql @SQL, N'@InnerKeyWord nvarchar(32)', @InnerKeyWord = @KeyWord
END

注意事项 #

• 传入的@WhereClause参数需要提前校验合法性，避免出现SQL语法错误或者注入风险，建议做敏感词过滤或者限制只允许传入指定格式的条件
• 如果不需要支持动态拼接自定义查询条件，尽量不要使用动态SQL，直接写静态查询的性能和安全性更高

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