問(wèn)題描述
我正在嘗試創(chuàng)建一個(gè)腳本，該腳本為我提供了一些數(shù)據(jù)庫(kù)的大小.我已經(jīng)創(chuàng)建了有效的原始查詢，但現(xiàn)在我想動(dòng)態(tài)創(chuàng)建它.
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.
我的腳本根據(jù)提交的變量創(chuàng)建一個(gè)臨時(shí)表.例如:
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')
腳本的其余部分然后遍歷此表中的行，并為我提供每個(gè)相應(yīng)數(shù)據(jù)庫(kù)的大小.
The rest of the script then loops over the rows in this table and gives me the size of each corresponding database.
我正在考慮在 sqlcmd 中傳遞一個(gè)變量，如下所示:
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)'
然而這給了我一個(gè)錯(cuò)誤:
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')'
請(qǐng)注意，行構(gòu)造函數(shù)列表用單引號(hào)括起來(lái)，導(dǎo)致 T-SQL 語(yǔ)法無(wú)效.所以解決方案是簡(jiǎn)單地刪除 SQLCMD 變量周圍的引號(hào):
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);
這篇關(guān)于SQL CMD:用括號(hào)和單引號(hào)傳遞變量的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！