动态SQL中插入字符串而非参数值的问题求助

问题场景 #

在存储过程的Catch块中尝试记录调用时的参数值，通过拼接动态字符串的方式构建参数内容，但插入日志表时，存入的是拼接的SQL表达式字符串，而非参数的实际值。

原代码 #

DECLARE @ErrorMessage VARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT,
        @ErrorProcedure VARCHAR(200),
        @ErrorLine INT,
        @ErrorNumber INT,
        @ErrorParams VARCHAR(MAX)='';

-- Get the error information
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
SET @ErrorProcedure = ERROR_PROCEDURE();
SET @ErrorLine = ERROR_LINE();
SET @ErrorNumber = ERROR_NUMBER();

DECLARE @SPParameter AS TABLE (Id INT IDENTITY PRIMARY KEY, ParaName VARCHAR(100))
INSERT INTO @SPParameter (ParaName)
SELECT [name]
FROM sys.parameters 
WHERE OBJECT_ID = OBJECT_ID(@ErrorProcedure)

DECLARE @minId INT, @maxId INT
SELECT @minId = MIN(Id),@maxId = MAX(Id) FROM @SPParameter

SET @ErrorParams = ''''

WHILE @minId <= @maxId
BEGIN
    SELECT @ErrorParams = @ErrorParams + ''+ParaName+' = ''+ISNULL(CAST('+CAST(ParaName AS varchar)+' AS VARCHAR),'''')+'', '
    FROM @SPParameter WHERE Id=@minId
    SET @minId = @minId + 1
END

SET @ErrorParams = LEFT(@ErrorParams, LEN(@ErrorParams) - 2)

SET @ErrorParams = @ErrorParams + ''''''

INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorNumber, ErrorParams)
VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorNumber, @ErrorParams);

日志表错误内容 #

'@Param1 = '+ISNULL(CAST(@Param1 AS VARCHAR),'') +
  ', @Param2 = '+ISNULL(CAST(@Param2 AS VARCHAR),'') +
  ', @Param3 = '+ISNULL(CAST(@Param3 AS VARCHAR),'') +
  ', @Param4 = '+ISNULL(CAST(@Param4 AS VARCHAR),''

问题原因 #

当前代码仅完成了SQL表达式字符串的拼接，并未执行该表达式来计算出参数的实际值，因此直接插入到日志表的是表达式文本，而非参数的真实取值。

解决方法 #

需要通过动态SQL执行拼接好的表达式，将计算后的参数值结果赋值给@ErrorParams，再插入日志表。修改后的完整代码如下：

DECLARE @ErrorMessage VARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT,
        @ErrorProcedure VARCHAR(200),
        @ErrorLine INT,
        @ErrorNumber INT,
        @ErrorParams VARCHAR(MAX)='';

-- 获取错误信息
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
SET @ErrorProcedure = ERROR_PROCEDURE();
SET @ErrorLine = ERROR_LINE();
SET @ErrorNumber = ERROR_NUMBER();

DECLARE @SPParameter AS TABLE (Id INT IDENTITY PRIMARY KEY, ParaName VARCHAR(100))
INSERT INTO @SPParameter (ParaName)
SELECT [name]
FROM sys.parameters 
WHERE OBJECT_ID = OBJECT_ID(@ErrorProcedure)

-- 拼接动态SQL语句：构建参数值的字符串表达式
DECLARE @DynamicSql NVARCHAR(MAX) = N'SET @Result = '''';'

SELECT @DynamicSql += N'
SET @Result += ISNULL(''' + ParaName + ' = '' + CAST(' + ParaName + ' AS VARCHAR(MAX)), ''' + ParaName + ' = NULL'') + '', '';'
FROM @SPParameter

-- 移除最后多余的逗号和空格
SET @DynamicSql = LEFT(@DynamicSql, LEN(@DynamicSql) - 3) + ';'

-- 执行动态SQL，获取实际参数值字符串
EXEC sp_executesql 
    @DynamicSql,
    N'@Result VARCHAR(MAX) OUTPUT',
    @Result = @ErrorParams OUTPUT

-- 插入日志表
INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorNumber, ErrorParams)
VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorNumber, @ErrorParams);

关键修改说明 #

1. 调整动态SQL拼接逻辑：直接构建赋值语句，不再包裹多余的单引号，让SQL引擎能执行表达式计算参数值。
2. 使用sp_executesql执行动态SQL：通过输出参数@Result获取计算后的参数值字符串，而非直接使用拼接的表达式文本。
3. 优化字符串处理：通过LEFT函数移除最后多余的逗号，确保参数字符串格式整洁。

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