将 sqlcmd 与脚本变量结合使用
更新日期： 2006 年 7 月 17 日
脚本中使用的变量称为脚本变量。 使用脚本变量，一个脚本可以应用于多个方案中。 例如，如果需要对多台服务器运行单个脚本，则可以用脚本变量来表示服务器名称，而不必为每台服务器修改脚本。 通过更改脚本变量表示的服务器名称，可以在不同的服务器上运行同一脚本。
可以使用 setvar 命令显式定义脚本变量，也可以使用 sqlcmd-v 选项隐式定义脚本变量。
本主题还包含有关使用 SET 在 Cmd.exe 命令提示符下定义环境变量的示例。
使用 setvar 命令设置脚本变量
setvar 命令用于定义脚本变量。 使用 setvar 命令定义的变量存储在内部。 不应将脚本变量与使用 SET 在命令提示符下定义的环境变量相混淆。 如果脚本引用的变量不是环境变量，或不是使用 setvar 定义的变量，则会返回错误消息，并将停止执行脚本。 有关详细信息，请参阅 sqlcmd 实用工具中的 -b 选项。
变量优先级（从低到高）
如果有多类变量具有相同的名称，则使用优先级最高的变量。
- 系统级环境变量
- 用户级环境变量
- 启动 sqlcmd 之前在命令提示符下设置的命令 shell (SET X=Y)
- sqlcmd-v X=Y
- :Setvar X Y
.gif "ms188714.note(zh-cn,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 |
"" |
* 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 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（包含值 $(tablename)）的 Col1 中插入一行。
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 日 |
|