以指令碼變數使用 sqlcmd
更新： 2006 年 7 月 17 日
用於指令碼中的變數稱為指令碼變數。指令碼變數可讓一個指令碼使用於多個狀況中。例如，如果您想要針對多個伺服器執行一個指令碼，而不針對每個伺服器修改指令碼，您可以使用指令碼變數來代表伺服器名稱。只要變更提供給指令碼變數的伺服器名稱，相同的指令碼就可以在不同的伺服器上執行。
指令碼變數可以使用 setvar 命令來明確定義，或使用 sqlcmd-v 選項來隱含定義。
本主題也包含在 Cmd.exe 命令提示字元中使用 SET 來定義環境變數的範例。
使用 setvar 命令設定指令碼變數
setvar 命令可用來定義指令碼變數。使用 setvar 命令定義的變數會儲存在內部。指令碼變數不應該與在命令提示字元中使用 SET 所定義的環境變數產生混淆。如果指令碼參考非環境變數的變數，或不是利用 setvar 定義的變數，則會傳回錯誤訊息且指令碼的執行會停止。如需詳細資訊，請參閱＜sqlcmd 公用程式＞中的 -b 選項。
變數優先順序 (由高至低)
如果有多個類型的變數具有相同的名稱，會使用具有最高優先順序的變數。
- 系統層級環境變數
- 使用者層級環境變數
- 在啟動 sqlcmd 之前，於命令提示字元設定的命令 Shell (SET X=Y)
- sqlcmd-v X=Y
- :Setvar X Y
.gif "ms188714.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 若要檢視環境變數，請在 [控制台] 中開啟 [系統]，然後按一下 [進階] 索引標籤。 |
隱含設定指令碼變數
當您透過含有相關的 sqlcmd 變數的選項啟動 sqlcmd 時，會將 sqlcmd 變數隱含地設定為使用選項所指定的值。在下列範例中，sqlcmd 透過 -l 選項啟動。這將會隱含地設定 SQLLOGINTIMEOUT 變數。
c:\> sqlcmd -l 60
您也可使用 -v 選項來設定存在於指令碼中的指令碼變數。在下列指令碼 (檔名為 testscript.sql) 中，ColumnName 為指令碼變數。
USE AdventureWorks;
SELECT c.$(ColumnName)
FROM Person.Contact x
WHERE c.ContactID < 5;
您可以接著指定要使用 -v 選項傳回的資料行名稱：
sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql
若要使用相同的指令碼傳回不同的資料行，請變更 ColumnName 指令碼變數的值。
sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql
指令碼變數名稱和值的指導方針
當您為指令碼變數命名時，請考慮下列指導方針：
- 變數名稱不能包含空白字元或引號。
- 變數名稱的格式不能和變數運算式的格式相同，例如 $(var)。
- 指令碼變數不區分大小寫。附註：
如果未指定任何值給 sqlcmd 環境變數，則會移除變數。使用不含值的 :setvar VarName 會清除變數。
當您指定指令碼變數的值時，請考慮下列指導方針：
- 如果字串值包含空格，使用 setvar 或 -v 選項所定義的變數值就必須用引號括住。
- 如果引號是變數值的一部分，則必須逸出。例如：:
setvar MyVar "spac""e"。
Cmd.exe SET 變數值和名稱的指導方針
使用 SET 定義的變數是 Cmd.exe 環境的一部分，並且可由 sqlcmd 參考。請考慮下列指導方針：
- 變數名稱不能包含空白字元或引號。
- 變數值可以包含空格或引號。
sqlcmd 指令碼變數
由 sqlcmd 定義的變數稱之為指令碼變數。下表列出 sqlcmd 指令碼變數。
| 變數 | 相關的選項 | R/W | 預設值 |
|---|---|---|---|
SQLCMDUSER* |
-U |
R |
"" |
SQLCMDPASSWORD* |
-P |
-- |
"" |
SQLCMDSERVER* |
-S |
R |
"DefaultLocalInstance" |
SQLCMDWORKSTATION |
-H |
R |
"ComputerName" |
SQLCMDDBNAME |
-d |
R |
"" |
SQLCMDLOGINTIMEOUT |
-l |
R/W |
"8" (秒) |
SQLCMDSTATTIMEOUT |
-t |
R/W |
"0" = 永遠等候 |
SQLCMDHEADERS |
-h |
R/W |
"0" |
SQLCMDCOLSEP |
-s |
R/W |
" " |
SQLCMDCOLWIDTH |
-w |
R/W |
"0" |
SQLCMDPACKETSIZE |
-a |
R |
"4096" |
SQLCMDERRORLEVEL |
-m |
R/W |
"0" |
SQLCMDMAXVARTYPEWIDTH |
-y |
R/W |
"256" |
SQLCMDMAXFIXEDTYPEWIDTH |
-Y |
R/W |
"0" = 無限制 |
SQLCMDEDITOR |
R/W |
"edit.com" |
|
SQLCMDINI |
R |
"" |
* 使用 :Connect 時，會設定 SQLCMDUSER、SQLCMDPASSWORD 和 SQLCMDSERVER。
R 表示在程式初始化期間只能設定該值一次。
R/W 表示可使用 setvar 命令來重設該值，且後續的命令將會使用新值。
範例
A. 在指令碼中使用 setvar 命令
有許多 sqlcmd 選項都可以在指令碼中使用 setvar 命令來控制。下列範例會建立指令碼 test.sql，其中的 SQLCMDLOGINTIMEOUT 變數設為 60 秒，而另一個指令碼變數 server 則設為 testserver。下列程式碼是在 test.sql 中。
:setvar SQLCMDLOGINTIMEOUT 60
:setvar server "testserver"
:connect $(server) -l $(SQLCMDLOGINTIMEOUT)
USE AdventureWorks;
SELECT FirstName, LastName
FROM Person.Contact;
The script is then called by using sqlcmd:
sqlcmd -i c:\test.sql
B. 以互動方式使用 setvar 命令
下列範例顯示如何使用 setvar 命令，以互動方式設定指令碼變數。
sqlcmd
:setvar MYDATABASE AdventureWorks
USE $(MYDATABASE);
GO
以下為結果集：
Changed database context to 'AdventureWorks'
1>
C. 在 sqlcmd 中使用命令提示字元環境變數
在下列範例中，設定了四個環境變數，然後再從 sqlcmd 進行呼叫。
C:\>SET tablename=Person.Contact
C:\>SET col1=FirstName
C:\>SET col2=LastName
C:\>SET title=Ms.
C:\>sqlcmd -d AdventureWorks
1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
2> FROM $(tablename)
3> WHERE Title ='$(title)'
4> GO
D. 在 sqlcmd 中使用使用者層級環境變數
下列範例在命令提示字元中設定了使用者層級環境變數 %Temp%，並將其傳遞至 sqlcmd 輸入檔。若要取得使用者層級環境變數，請在 [控制台] 中按兩下 [系統]。按一下 [進階] 索引標籤，再按一下 [環境變數]。
下列程式碼是在輸入檔 c:\testscript.txt 中：
:OUT $(MyTempDirectory)
USE AdventureWorks;
SELECT FirstName
FROM AdventureWorks.Person.Contact
WHERE ContactID < 5;
下列程式碼是在命令提示字元中輸入的：
C:\ >SET MyTempDirectory=%Temp%\output.txt
C:\ >sqlcmd -i C:\testscript.txt
下列結果會傳送到輸出檔 C:\Documents and Settings\<user>\Local Settings\Temp\output.txt。
Changed database context to 'AdventureWorks'.
FirstName
--------------------------------------------------
Gustavo
Catherine
Kim
Humberto
(4 rows affected)
E. 使用啟動指令碼
sqlcmd 啟動指令碼會在 sqlcmd 啟動時執行。下列範例會設定環境變數 SQLCMDINI。以下是 init.sql. 的內容。
SET NOCOUNT ON
GO
DECLARE @nt_user_name nvarchar(128)
SET @nt_user_name = (SELECT rtrim(convert(nvarchar(128), nt_user_name))
FROM sys.dm_exec_sessions WHERE session_id = @@SPID);
SELECT @nt_user_name + ' is connected to ' +
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('servername'))) + ' (' +
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('productversion'))) + ')';
:setvar SQLCMDMAXFIXEDTYPEWIDTH 100
SET NOCOUNT OFF
GO
:setvar SQLCMDMAXFIXEDTYPEWIDTH
這會在 sqlcmd 啟動時呼叫 init.sql 檔案。
C:\> SET sqlcmdini=c:\init.sql
>1 Sqlcmd
以下是輸出。
>1 < user > is connected to < server > (9.00.2047.00)
注意-X 選項會停用啟動指令碼功能。
F. 使用變數展開
下列範例顯示以 sqlcmd 變數的形式來處理資料。
USE AdventureWorks;
CREATE TABLE AdventureWorks.dbo.VariableTest
(
Col1 nvarchar(50)
);
GO
在 dbo.VariableTest 的 Col1 中插入一個資料列，內含值 $(tablename)。
INSERT INTO AdventureWorks.dbo.VariableTest(Col1)
VALUES('$(tablename)');
GO
在 sqlcmd 提示字元中，當沒有任何變數設定為等於 $(tablename) 時，下列陳述式會傳回資料列。
C:\> sqlcmd
>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
>2 GO
>3 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
>4 GO
以下為結果集：
>1 Col1
>2 ------------------
>3 $(tablename)
>4
>5 (1 rows affected)
假使變數 MyVar 設定為 $(tablename)。
>6 :setvar MyVar $(tablename)
下列陳述式會傳回該資料列，而且還會傳回訊息「'tablename' 指令碼變數未定義」。
>6 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
>7 GO
>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
>2 GO
下列陳述式會傳回資料列。
>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(MyVar)';
>2 GO
>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(MyVar)';
>2 GO
變更歷程記錄
| 版本 | 歷程記錄 |
|---|---|
2006 年 7 月 17 日 |
|