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'

score 2 · Accepted Answer · edited Jun 08 '16 at 15:47

2

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

edited Jun 08 '16 at 15:47

Dave C

7,272
1
19
30

answered Jun 08 '16 at 15:03

user212514

3,110
1
15
11

`@cat` is used inside `@sql` – Ivan Starostin Jun 08 '16 at 15:28
1

I fixed the `@cat` within `@sql`. – Dave C Jun 08 '16 at 15:48
So the way I have it, with the arguments following `sp_executesql`, what's that for? It's the way I found online, but your way is what works. – Andy Jun 08 '16 at 16:36
There was definitely a problem in that you were concatenating `@cat` even though it was `null` which means that `@sql` was null (and why I suggested adding the `print`. The variable substitution that you were attempting may have worked if you had `@cat` inside of `@sql` rather than concatenating it all together. I'm just not certain that you can successfully use `@cat` in the table names with the variable substitution. – user212514 Jun 08 '16 at 18:01

Weave variables into table names in TSQL

1 Answers1