Dynamic SQL - Use declared VARCHAR in SET SQL string-CodePudding

How to use the declared variable @CodeID inside the SQL string? When I run following statement I get the "Invalid object name (..)" error.

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

CodePudding user response：

It looks to me that you need two levels of dynamic SQL, with the first level inserting the database name (from #folders), and the second level inserting a constructed table name (based on the CodeType column of the database-local bla.Field table).

I do not know of any way to parameterize database names or table names using sp_executesql, so I'm sticking with build-up dynamic SQL and EXEC (). (If someone makes a case for preferring sp_executesql over EXEC when not useing parameters, then it may be worth the switch.)

Try something like:

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

This implements dynamic SQL within dynamic SQL. Doubled quotes in the outer sql template become single quotes in the inner sql. The original posted code seemed to be missing a schema qualifier and alias for the Document table, so I inserted them ("bla" and "D"). I also added QUOTENAME around the injected names as suggested by Larnu.

The first level of dynamic sql would generate something like:

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

The second level would generate something like:

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

Note that each loop iteration will generate a separate result. If you wish to combine results, you will need to define a #temp table, insert the individual results into that table, and then select the combined results at the end of your script.

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.

CodePudding user response：

Try this:

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'
    
    SET @Sql = REPLACE(@Sql, '@CodeID', CONVERT(varchar, <CodeID Value Here>)
    EXEC ( @Sql )
    SET @FolderID = @FolderID   1
END