Dynamic MS SQL Variable Parsing incorrectly

问题:

I have got an issue with Dynamic Variable Parsing incorrectly and I don't know a way around it.

Here is an example:

DECLARE @tableName varchar(20),
    @columnName varchar(20),
    @prompt varchar(20)

DECLARE @dynSQL varchar(500)


set @tableName = '[A1-ExciseESN]'
set @columnName = '[Anode Excise ESN (A1)]'

@dynSQL = 'INSERT INTO ' + @tableName +'
([trav num], '+@columnName+')
Select [trav num], '+@columnName+'
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ('+@columnName+'
   )
   ) AS PVTTable
     where '+@columnName+' is not null and [trav num] not in (select [trav num] from '+@tableName+')'


print @dynSQL

Here is the output of the print @dynSQL

INSERT INTO [A1-ExciseESN]
([trav num], [Anode Excise ESN (A) 
Select [trav num], [Anode Excise ESN (A
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ([Anode Excise ESN (A
   )
   ) AS PVTTable
     where [Anode Excise ESN (A is not null and [trav num] not in (select [trav num] from [A1-ExciseESN])

I've tried several methods but can't seem to make it work. Thanks in advance for the help!

回答1:

1. Use correct types SYSNAME for idetifiers, NVARCHAR(MAX) for query
2. Use REPLACE instead of concatenating it will be much easier to debug especially when you need string literals
3. Consider using QUOTENAME function

Code:

DECLARE 
    @tableName  SYSNAME,
    @columnName SYSNAME,
    @prompt     VARCHAR(20),
    @dynSQL     NVARCHAR(MAX);

SET @tableName  = QUOTENAME('A1-ExciseESN');
SET @columnName = QUOTENAME('Anode Excise ESN (A1)');

SET @dynSQL = 
N'INSERT INTO  <tableName> 
 ([trav num], <columnName>)
Select [trav num], <columnName>
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN (<columnName>)
   ) AS PVTTable
     where <columnName> is not null 
        and [trav num] not in (select [trav num] from <tableName> )'

SET @dynSQL = REPLACE(REPLACE(@dynSQL, '<tableName>', @tableName),
               '<columnName>', @columnName);

PRINT @dynSQL;

LiveDemo

Output:

INSERT INTO  [A1-ExciseESN] 
 ([trav num], [Anode Excise ESN (A1)])
Select [trav num], [Anode Excise ESN (A1)]
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ([Anode Excise ESN (A1)])
   ) AS PVTTable
     where [Anode Excise ESN (A1)] is not null 
       and [trav num] not in (select [trav num] from [A1-ExciseESN] )

回答2:

You've defined:

@columnName varchar(20),

But you're assigning:

set @columnName = '[Anode Excise ESN (A1)]'

columnname needs to be at least varchar(23). the column name you're using is 23 characters long.