'Dynamic SQL - Use declared VARCHAR in SET SQL string

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
sqlsql-servertsql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions