Sql Cmd: Pass Variables With Brackets And Single Quotes

I am trying to create a script that gives me the size of some databases. I have created the original query which works but now i want to make it dynamically. My script creates a te

Solution 1:

Consider this code:

createtable #tables (idx intIDENTITY(1,1), valuex varchar(256))
INSERTINTO #tables (valuex) values'$(variables)'

After variable substitution, it becomes:

createtable #tables (idx intIDENTITY(1,1), valuex varchar(256))
INSERTINTO #tables (valuex) values'('PARTS'),('PARTS_Master'),('PARTS2_4'),('PARTS2_7'),('Projects')'

Note the list of row constructors is enclosed in single quotes, resulting in invalid T-SQL syntax. So the solution is to simply remove the quotes around the SQLCMD variable:

CREATETABLE #tables (idx intIDENTITY(1,1), valuex varchar(256))
INSERTINTO #tables (valuex) VALUES $(variables);