sqlcmd - 搭配指令碼變數使用

文章
07/12/2024

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

用於指令碼中的變數稱為指令碼變數。指令碼變數可讓一個指令碼使用於多個狀況中。例如，如果您想要針對多個伺服器執行一個指令碼，而不針對每個伺服器修改指令碼，您可以使用指令碼變數來代表伺服器名稱。只要變更提供給指令碼變數的伺服器名稱，相同的指令碼就可以在不同的伺服器上執行。

指令碼變數可以使用 setvar 命令來明確定義，或使用 sqlcmd -v 選項來隱含定義。

本文也包含在 SET 命令提示字元中使用 SET 來定義環境變數的範例。

使用 setvar 命令設定指令碼變數

setvar 命令可用來定義指令碼變數。使用 setvar 命令定義的變數會儲存在內部。指令碼變數不應與在命令提示字元中使用 SET 所定義的環境變數產生混淆。如果指令碼參考非環境變數的變數，或不是使用 setvar所定義的變數，則會傳回錯誤訊息並停止執行指令碼。如需詳細資訊，請參閱 sqlcmd 中的 -b 選項。

變數優先順序 (由低至高)

如果有多個類型的變數具有相同的名稱，會使用具有最高優先順序的變數。

系統層級環境變數
使用者層級環境變數
在啟動 sqlcmd 之前，於命令提示字元設定命令殼層 (SET X=Y)
sqlcmd -v X=Y
:Setvar X Y

注意

若要檢視環境變數，請在 [控制台] 中開啟 [系統]，然後選取 [進階] 索引標籤。

隱含設定指令碼變數

當您透過含有 sqlcmd 相關變數的選項啟動 sqlcmd 時，會將 sqlcmd 變數隱含設定為使用該選項所指定的值。在下列範例中， sqlcmd 透過 -l 選項啟動。這將會隱含地設定 SQLLOGINTIMEOUT 變數。

sqlcmd -l 60

您也可以使用 -v 選項來設定存在於指令碼中的指令碼變數。在下列指令碼 (檔名為 testscript.sql) 中， ColumnName 為指令碼變數。

USE AdventureWorks2022;

SELECT x.$(ColumnName)
FROM Person.Person x
WHERE x.BusinessEntityID < 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 ²	},

¹SQLCMDUSER、SQLCMDPASSWORD 和 SQLCMDSERVER 會在使用 :Connect 時設定。

² 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 AdventureWorks2022;

SELECT FirstName, LastName
FROM Person.Person;

隨後會使用 sqlcmd 呼叫指令碼：

sqlcmd -i c:\test.sql

B. 以互動方式使用 setvar 命令

下列範例顯示如何使用 setvar 命令，以互動方式設定指令碼變數。

sqlcmd
:setvar MYDATABASE AdventureWorks2022
USE $(MYDATABASE);
GO

結果集如下所示。

Changed database context to 'AdventureWorks2022'
1>

C. 在 sqlcmd 內使用命令提示字元環境變數

在下列範例中，設定了四個環境變數 are ，然後從 sqlcmd 進行呼叫。

SET tablename=Person.Person
SET col1=FirstName
SET col2=LastName
SET title=Ms.
sqlcmd -d AdventureWorks2022
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 AdventureWorks2022;

SELECT FirstName
FROM AdventureWorks2022.Person.Person
WHERE BusinessEntityID < 5;

下列程式碼是在命令提示字元中輸入的：

SET MyTempDirectory=%Temp%\output.txt
sqlcmd -i C:\testscript.txt

結果看起來會類似下列輸出檔案 C:\Documents and Settings\<user>\Local Settings\Temp\output.txt。

Changed database context to 'AdventureWorks2022'.
FirstName
--------------------------------------------------
Gustavo
Catherine
Kim
Humberto

(4 rows affected)

E. 使用啟動指令碼

啟動 sqlcmd 時，會執行 sqlcmd 啟動指令碼。下列範例會設定環境變數 SQLCMDINI。這是下者的內容： init.sql.

SET NOCOUNT ON
GO

DECLARE @nt_username nvarchar(128)
SET @nt_username = (SELECT rtrim(convert(nvarchar(128), nt_username))
FROM sys.dm_exec_sessions WHERE spid = @@SPID)
SELECT  @nt_username + ' is connected to ' +
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('servername'))) +
' (' +`
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('productversion'))) +
')'
:setvar SQLCMDMAXFIXEDTYPEWIDTH 100
SET NOCOUNT OFF
GO

:setvar SQLCMDMAXFIXEDTYPEWIDTH

這會在 init.sql 啟動時呼叫 sqlcmd 檔案。

SET sqlcmdini=c:\init.sql
sqlcmd

以下是輸出。

1> <user> is connected to <server> (9.00.2047.00)

注意

-X 選項會停用啟動指令碼功能。

F. 變數展開

下列範例顯示如何以 sqlcmd 變數的形式來使用資料。

USE AdventureWorks2022;
GO
CREATE TABLE AdventureWorks2022.dbo.VariableTest (Col1 NVARCHAR(50));
GO

在 Col1 的 dbo.VariableTest 中插入一個資料列，內含值 $(tablename)。

INSERT INTO AdventureWorks2022.dbo.VariableTest (Col1)
VALUES ('$(tablename)');
GO

在 sqlcmd 提示字元中，當沒有任何變數設定等於 $(tablename) 時，下列陳述式會傳回資料列，同時傳回訊息「'tablename' 指令碼變數未定義」。預設不設定 sqlcmd 旗標 -b。如果 -b 已設定，則 sqlcmd 會在「未定義變數」錯誤後終止。

sqlcmd
1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
2> GO
3> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
4> GO