CODEWITHSUNDEEP
sql-servervariablesescapingsqlcmd
Kevin Joos
Kevin Joos

Reputation: 3

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 temp table based on the variable that was submitted. for example:

        create table #temptbl (idx int IDENTITY(1,1), valuex varchar(256))
        INSERT INTO #temptbl (valuex) values ('PARTS'),('PARTS_Master'),('PARTS2_4'),('PARTS2_7'),('Projects')

The rest of the script then loops over the rows in this table and gives me the size of each corresponding database.

I was looking into passing a variable in sqlcmd like so:

sqlcmd -v variables ="('PARTS'),('PARTS_Master'),('PARTS2_4'),('PARTS2_7'),('Projects')" -S MYSERVERNAME\sqlexpress -i DatabaseSize.sql -d Parts

and then in my sql script I changed it like this:

        create table #tables (idx int IDENTITY(1,1), valuex varchar(256))
        INSERT INTO #tables (valuex) values '$(variables)'

This however gives me an error:

Msg 102, Level 15, State 1, Server ServerName\SQLEXPRESS, Line 16
Incorrect syntax near '('.

Thank you for the assistance.

Upvotes: 0

Views: 500

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Consider this code:

create table #tables (idx int IDENTITY(1,1), valuex varchar(256))
INSERT INTO #tables (valuex) values '$(variables)'

After variable substitution, it becomes:

create table #tables (idx int IDENTITY(1,1), valuex varchar(256))
INSERT INTO #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:

CREATE TABLE #tables (idx int IDENTITY(1,1), valuex varchar(256))
INSERT INTO #tables (valuex) VALUES $(variables);

Upvotes: 1

Related Questions