无法将列名作为参数传递给sp_executesql

问题描述

我在执行以下代码时遇到麻烦，这给我一个错误，如下所示:

I'm having trouble executing the below piece of code, it's giving me an error as below:

我可以尝试使用动态SQL来实现登录，但是想尝试 sp_executesql 方法.请让我知道我是否遇到语法错误，或者不应该将表名作为参数传递?

I can try implementing the login using dynamic SQL, but wanted to try the sp_executesql method. Please let me know if I'm having some syntax error or I'm not supposed to pass table names as parameters?

DECLARE @SQL NVARCHAR(4000)= '';
SET @SQL = N'--INSERT INTO #missingkeys ( SOURCE_KEY,[ROWCOUNT] )
SELECT  S.[SOURCE_KEY], COUNT(1) AS [ROWCOUNT] FROM (SELECT DISTINCT   @SK AS [SOURCE_KEY]
FROM [PDA].@ST ) AS S
LEFT JOIN [PDA].@MT   AS T
ON T.[SOURCE_KEY] = S.[SOURCE_KEY]
GROUP BY S.[SOURCE_KEY]';
DECLARE @SOURCETABLE NVARCHAR(255)= 'FACT';
DECLARE @SOURCE_KEY NVARCHAR(255)= 'KEY', @MAP_TABLE NVARCHAR(255)= 'DimMap';
EXEC sp_executesql
 @SQL,
 N'@SK nvarchar(255), @ST nVARCHAR(255), @MT nVARCHAR(255)',
 @SK = @SOURCE_KEY,
 @ST = @SOURCETABLE,
 @MT = @MAP_TABLE;

推荐答案

您不能将列作为参数，这与任何对象名(表，存储过程等)相同.

You can't have columns as parameters, same for any object name (table, stored procedure, ...).

您将必须使该语句动态化，即在SQL字符串中设置列名称的格式:

You will have to make the statement dynamic, i.e. format the column name in the SQL string:

SET @SQL =
    N'SELECT '+
        'S.[SOURCE_KEY],'+
        'COUNT(1) AS [ROWCOUNT] '+
     'FROM ('+
         'SELECT DISTINCT '+
             QUOTENAME(@SK)+' AS [SOURCE_KEY] '+
             '...'; -- the rest of your statement

PS:使用 QUOTENAME 转义对象名称以避免SQL注入.

PS: Use QUOTENAME to escape object names to avoid SQL Injection.

这篇关于无法将列名作为参数传递给sp_executesql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！