問題描述
我正在嘗試創建一個腳本，該腳本為我提供了一些數據庫的大小.我已經創建了有效的原始查詢，但現在我想動態創建它.
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模板網！