問題描述

我正在嘗試創建一個腳本，該腳本為我提供了一些數據庫的大小.我已經創建了有效的原始查詢，但現在我想動態創建它.

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

這篇關于SQL CMD:用括號和單引號傳遞變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！