将 sqlcmd 与脚本变量配合使用

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric SQL 数据库

sqlcmd 实用工具允许你输入 Transact-SQL 语句、系统过程和脚本文件。

在 sqlcmd 中编写变量脚本

脚本中使用的变量称为脚本变量。 使用脚本变量，一个脚本可以应用于多个方案中。 例如，如果需要对多台服务器运行单个脚本，则可以用脚本变量来表示服务器名称，而不必为每台服务器修改脚本。 通过更改脚本变量表示的服务器名称，可以在不同的服务器上运行同一脚本。

可以使用 setvar 命令显式地定义脚本变量，也可以使用 sqlcmd -v 选项隐式地定义。

本文还包括使用 SET命令行定义环境变量的示例。

使用 setvar 命令设置脚本变量

该 setvar 命令定义脚本变量。 使用 setvar 命令定义的变量存储在内部。 不应将脚本变量与使用 SET 在命令提示符下定义的环境变量相混淆。 如果脚本引用的变量不是环境变量或未使用 setvar定义变量，则返回错误消息并停止执行脚本。 有关详细信息，请参阅 sqlcmd 中的 -b 选项。

变量优先级（从低到高）

如果有多类变量具有相同的名称，则使用优先级最高的变量。

1. 系统级环境变量
2. 用户级环境变量
3. 启动 sqlcmd 之前在命令提示符处设置的命令 shell (SET X=Y)
4. sqlcmd -v X=Y
5. :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 脚本变量

¹ 使用 :Connect 时设置 SQLCMDUSER、SQLCMDPASSWORD 和 SQLCMDSERVER。

² R 表示在程序初始化过程中只能设置一次值。

³ R/W 表示可以使用命令重置 setvar 值，后续命令使用新值。

示例

本文中的代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可以从 Microsoft SQL Server 示例和社区项目 主页下载该数据库。

答： 在脚本中使用 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)

F. 变量扩展

下面的示例演示了以 sqlcmd 变量的形式处理数据。

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

在 dbo.VariableTest 的 Col1 中插入一个包含值 $(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

结果集如下。

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

相关内容

• 详细了解 GitHub 上的 sqlcmd （Go） 实用工具
• 快速入门：使用 Docker 运行 SQL Server Linux 容器映像
• 启动 sqlcmd 实用程序
• 使用 sqlcmd 从脚本文件执行 T-SQL
• 使用实用工具
• 将 sqlcmd 与脚本变量配合使用
• 使用 sqlcmd 连接到 SQL Server
• 使用查询编辑器编辑 SQLCMD 脚本
• 管理作业步骤
• 创建 CmdExec 作业步骤