Dynamic SQL - Use declared VARCHAR in SET SQL string

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.

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.