CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Arvind
Arvind

Reputation: 301

Cannot pass column name as parameter to sp_executesql

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

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@ST'.

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;

Upvotes: 4

Views: 1365

Answers (2)

cloudsafe
cloudsafe

Reputation: 2504

You can pass tables if you write the data to an identical user-defined table type. The parameter must be READONLY:

CREATE TYPE [dbo].[t] AS TABLE([a] [int] NOT NULL PRIMARY KEY CLUSTERED)
create table #t (a int)
insert into #t values (1), (2), (3)
exec sp_executesql N'select * from #t'
declare @t t
insert into @t select a from #t
exec sp_executesql N'Select * from @p1', N'@p1 t readonly', @t

Upvotes: 1

TT.
TT.

Reputation: 16137

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

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: Use QUOTENAME to escape object names to avoid SQL Injection.

Upvotes: 7

Related Questions