SQL CMD:用括号和单引号传递变量

2021-09-16 00:00:00 variables escaping sql-server sqlcmd

我正在尝试创建一个脚本，该脚本为我提供了一些数据库的大小.我已经创建了有效的原始查询，但现在我想动态创建它.

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.

我正在考虑在 sqlcmd 中传递一个变量，如下所示:

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

然后在我的 sql 脚本中我像这样改变它:

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 '('.

感谢您的帮助.

推荐答案

考虑以下代码:

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

变量替换后变成:

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

请注意，行构造函数列表用单引号括起来，导致 T-SQL 语法无效.所以解决方案是简单地删除 SQLCMD 变量周围的引号:

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);

相关文章