動態SQL-在SETSQL字串中使用宣告的VARCHAR

如何在 SQL 字串中使用宣告的變數@CodeID ？當我運行以下陳述句時，我收到“無效的物件名稱 (..)”錯誤。

WHILE @FolderID <= @FolderMaxID
BEGIN
    SELECT @Db  = Db 
    FROM #Folders 
    WHERE ID =  @FolderID

    SET @Sql = N'

        DECLARE @CodeID NVARCHAR(256)

        SELECT TOP(1)  @CodeID=CodeType
        FROM '   @Db   '.bla.Field 
        WHERE Name= ''Example''

        SELECT DISTINCT C.Name
        FROM '   @Db   '.Document 
            INNER JOIN '   @Db   '.bla.Code_@CodeID  C  ON D.ID = C.ID'
        
    EXEC ( @Sql )
    SET @FolderID = @FolderID   1
END

uj5u.com熱心網友回復：

在我看來，您需要兩級動態 SQL，第一級插入資料庫名稱（來自#folders），第二級插入構造的表名（基于資料庫本地 bla.Field 的 CodeType 列桌子）。

我不知道使用 sp_executesql 引數化資料庫名稱或表名稱的任何方法，所以我堅持使用構建動態 SQL 和 EXEC()。（如果有人提出在不使用引數時更喜歡 sp_executesql 而不是 EXEC，那么可能值得切換。）

嘗試類似：

WHILE @FolderID <= @FolderMaxID
BEGIN
    SELECT @Db  = Db 
    FROM #Folders 
    WHERE ID =  @FolderID

    SET @Sql = N'

        DECLARE @CodeID NVARCHAR(256)

        SELECT TOP(1)  @CodeID=CodeType
        FROM '   QUOTENAME(@Db)   '.bla.Field 
        WHERE Name= ''Example''

        DECLARE @Sql2 NVARCHAR(MAX) = N''
            SELECT DISTINCT C.Name
            FROM '   QUOTENAME(@Db)   '.bla.Document D
                INNER JOIN '   QUOTENAME(@Db)   '.bla.''   QUOTENAME(''Code_''   @CodeID)   '' C  ON D.ID = C.ID
        ''
        EXEC @sql2
        '
    
    EXEC ( @Sql )
    SET @FolderID = @FolderID   1
END

這在動態 SQL 中實作了動態 SQL。外部 sql 模板中的雙引號成為內部 sql 中的單引號。原始發布的代碼似乎缺少 Document 表的模式限定符和別名，所以我插入了它們（“bla”和“D”）。我還按照 Larnu 的建議在注入的名稱周圍添加了 QUOTENAME。

第一級動態 sql 將生成如下內容：

SELECT TOP(1)  @CodeID=CodeType
FROM [db1].bla.Field 
WHERE Name= 'Example'

DECLARE @Sql2 NVARCHAR(MAX) = N'
    SELECT DISTINCT C.Name
    FROM [db1].bla.Document D
        INNER JOIN [db1].bla.'   QUOTENAME('Code_'   @CodeID)   ' C  ON D.ID = C.ID
'
EXEC @sql2

第二級會產生類似的東西：

SELECT DISTINCT C.Name
FROM [db1].bla.Document D
    INNER JOIN [db1].bla.[Code_Table1] C  ON D.ID = C.ID

請注意，每次回圈迭代都會生成一個單獨的結果。如果您希望合并結果，您需要定義一個#temp 表，將單個結果插入該表，然后在腳本末尾選擇合并的結果。

Note that I haven't tested the specific code above, so it might need some debugging (add "PRINT @sql2" before the EXEC) if it doesn't work straight out.

ADDENDUM

Per @trenton-ftw comments below, an out parameter can be used to capture the result of the first query so that it may be included in the second query without the need for nesting. Two executions are still required. Below is a revised example.

DECLARE @Folders TABLE (ID INT IDENTITY(1,1), Db sysname)
INSERT @Folders VALUES ('db1'), ('db2')

DECLARE @SearchName NVARCHAR(256) = 'Example' 
DECLARE @Db sysname
DECLARE @Sql NVARCHAR(MAX)
DECLARE @CodeID NVARCHAR(256)

DECLARE @FolderMaxID INT = (SELECT MAX(ID) FROM @Folders)
DECLARE @FolderID INT = 1
WHILE @FolderID <= @FolderMaxID
BEGIN
    SELECT @Db = Db 
    FROM @Folders 
    WHERE ID = @FolderID

    SET @Sql = N'
        SET @CodeID = @SearchName   ''-Test''
        --SELECT TOP(1) @CodeID = CodeType
        --FROM '   QUOTENAME(@Db)   '.bla.Field 
        --WHERE Name = @SearchName'
    PRINT @Sql
    EXEC sp_executesql @Sql,
        N'@SearchName NVARCHAR(256), @CodeID NVARCHAR(256) OUTPUT',
        @SearchName, @CodeID OUTPUT

    SET @Sql = N'
        --SELECT DISTINCT C.Name
        --FROM '   QUOTENAME(@Db)   '.bla.Document D
        --    INNER JOIN '   QUOTENAME(@Db)   '.bla.'   QUOTENAME('Code_'   @CodeID)   ' C  ON D.ID = C.ID'
    PRINT @Sql
    EXEC sp_executesql @sql

    SET @FolderID = @FolderID   1
END

For demo purposes, I also parameterized the search name as an input parameter and added some temporary code to make it stand-alone testable. A final version would uncomment the actual sql, and remove the print statements and the test @CodeID assignemnt.

標籤：sql sql服务器 tsql

上一篇：SQLServer組行

下一篇：每次超過集合中的數字時，為列中的每個值添加一個？