sql-server - the - t sql



動態MS SQL變量解析不正確 (2)

  1. 使用正確的類型SYSNAME作為標識符, NVARCHAR(MAX)用於查詢
  2. 使用REPLACE而不是連接它會更容易調試,特別是當你需要字符串文字時
  3. 考慮使用QUOTENAME函數

碼:

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

https://src-bin.com

我錯誤地解決了動態變量分析問題,我不知道解決方法。

這裡是一個例子:

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], '[email protected]columnName+')
Select [trav num], '[email protected]columnName+'
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ('[email protected]columnName+'
   )
   ) AS PVTTable
     where '[email protected]columnName+' is not null and [trav num] not in (select [trav num] from '[email protected]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])

我嘗試了幾種方法,但似乎無法使其工作。 先謝謝您的幫助!


Answer #1

你已經定義了:

@columnName varchar(20),

但是你正在分配:

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

列名至少需要varchar(23)。 您使用的列名稱長度為23個字符。





sql-server-2012