动态 MS SQL 变量解析不正确 [英] 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.

这是一个例子:

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

这是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. 使用正确的类型 SYSNAME 作为标识符，NVARCHAR(MAX) 作为查询
2. 使用 REPLACE 而不是连接它会更容易调试，尤其是当您需要字符串文字时
3. 考虑使用QUOTENAME函数

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

代码:

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

输出:

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] )

这篇关于动态 MS SQL 变量解析不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！