Weave variables into table names in TSQL

Question

I'm attempting to learn to use dynamic SQL to automate what would otherwise require a lot of typing. However, this would include putting variables directly into table names (not as the whole table name).

When running the below query directly in SSMS, I get the output "Command(s) completed successfully"... but I'd rather get the query output. Where am I going wrong?

DECLARE @sql NVARCHAR(MAX)
DECLARE @cat NVARCHAR(25)
DECLARE @type NVARCHAR(25)

SET @sql = '
            SELECT EntityID, ''@cat'' AS c, Subcategory'+@type+' 
            FROM WCO..Entity'+@cat+' a
            JOIN WCO..Entity'+@cat+'Subcategory b ON a.Entity'+@cat+'ID = b.Entity'+@cat+'ID
            JOIN WCO..'+@cat+'Subcategory c ON b.'+@cat+'SubcategoryID = c.'+@cat+'SubcategoryID
            WHERE
                EntityID IN Ent_ID IN (728456,762360)
            '

EXECUTE sp_executesql @sql, N'@cat NVARCHAR(25), @type NVARCHAR(25)', 'AdverseMedia', 'Label'

Answer 1

When you're constructing @sql you're concatenating @cat and @type into the string, however, they're uninitialized. As a result, your @sql variable is null when you go to execute (try using print @sql right before the sp_executesql). You're looking for more like (note the initializations in the declarations):

DECLARE @sql NVARCHAR(MAX)
DECLARE @cat NVARCHAR(25) = 'AdverseMedia'
DECLARE @type NVARCHAR(25) = 'Label'

SET @sql = '
            SELECT EntityID, '''+@cat+''' AS c, Subcategory'+@type+' 
            FROM WCO..Entity'+@cat+' a
            JOIN WCO..Entity'+@cat+'Subcategory b ON a.Entity'+@cat+'ID = b.Entity'+@cat+'ID
            JOIN WCO..'+@cat+'Subcategory c ON b.'+@cat+'SubcategoryID = c.'+@cat+'SubcategoryID
            WHERE
                EntityID IN Ent_ID IN (728456,762360)
            '
PRINT @sql
EXECUTE sp_executesql @sql