动态 SQL Server 变量从游标解析不正确 [英] Dynamic SQL Server Variable Parsing Incorrectly from Cursor

问题描述

我在解析由查找表填充的查找游标中的变量时遇到问题.

I'm having trouble parsing a variable from a lookup cursor populated by a lookup table.

这与我之前的问题非常相似:

It is very similar to my previous question here:

动态 MS SQL 变量解析不正确

但现在我从查找表中提供 @tablename 和 @columnname，这导致我错过了 [ 和] 在列名周围.

But now I'm supplying the @tablename and @columnname from a lookup table, which is causing me to miss the [ and ] around the column names.

DECLARE 
    @tableName SYSNAME,
    @columnName SYSNAME,
    @col2 SYSNAME,
    @prompt SYSNAME,
    @dynSQL varchar(MAX);

Declare cLookup CURSOR FOR
    SELECT * 
    FROM LookupTbl

OPEN cLookup

FETCH NEXT FROM cLookup INTO @tableName, @columnName, @prompt

WHILE @@FETCH_STATUS = 0
BEGIN
    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
    Print 'Done with '+ @tableName
    FETCH NEXT FROM cLookup
    INTO @tableName, @columnName, @prompt

end
Close cLookup
Deallocate cLookup

查找表:

TableName       ColumnName                  Prompt
A1-ExciseESN    Anode Excise ESN (A1)       NULL
A1-Excise DT    Anode ExciseDate&Time(A1)   NULL
A1-Excise DT-1  AnodeExcieDate&Time(A1)-1   NULL
StackFixture    Stack Fixture               NULL

见下面的输出.我缺少列名周围的 [ 和 ].

See output below. I am missing the [ and ] around the column names.

输出:

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)

推荐答案

有一个名为 QUOTENAME 的函数可以做到这一点:

There is a function called QUOTENAME which will do exactly that:

SET @dynSQL = REPLACE(
               REPLACE(
                @dynSQL
                ,'<tablename>'
                , QUOTENAME(@tablename)
                )
              ,'<columnname>',QUOTENAME(@columnname)
               )

例如select QUOTENAME('abc') = '[abc]'

QUOTENAME (Transact-SQL)

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