如何判断变量 (-v) 是否在命令行 (SQLCMD) 上定义

发布于 2024-09-27 02:45:12 字数 309 浏览 0 评论 0原文

有没有办法判断变量是否使用 SQLCMD 在命令行上定义？

这是我的命令行：

sqlcmd -vDB="EduC_E100" -i"Z:\SQL Common\Admin\ProdToTest_DB.sql"

在 ProdToTest_DB.sql 内部，我想设置某种条件 IF 来检查变量是否不存在，如果不存在则定义它。

IF NOT $(DB)
:setvar DB "C_Q200"
END

我想允许脚本从命令行和 SSMS 内部运行。

提前致谢。

原文

Is there a way to tell if a variable is defined on command line using SQLCMD?

Here is my command line:

sqlcmd -vDB="EduC_E100" -i"Z:\SQL Common\Admin\ProdToTest_DB.sql"

Inside ProdToTest_DB.sql I would like to set some sort of conditional IF to check if the variable does not exists and define it if it does not.

IF NOT $(DB)
:setvar DB "C_Q200"
END

I want to allow the script to be run both from command line and inside of SSMS.

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

思念绕指尖 2024-10-04 02:45:12

我在许多命令行变量相关脚本中使用了以下例程的变体。这里，“DataPath”是必需的值。

DECLARE @Test_SQLCMD  varchar(100)

--  Confirm that SQLCMD values have been set (assume that if 1 is, all are)
SET @Test_SQLCMD = '$(DataPath)'

IF reverse(@Test_SQLCMD) = ')htaPataD(

    --  SQLCMD variables have not been set, crash and burn!
    RAISERROR('This script must be called with the required SQLCMD variables!', 20, 1) with log

I've used variants of the following routine in numerous command-line-variable-dependant scripts. Here, "DataPath" is the required value.

DECLARE @Test_SQLCMD  varchar(100)

--  Confirm that SQLCMD values have been set (assume that if 1 is, all are)
SET @Test_SQLCMD = '$(DataPath)'

IF reverse(@Test_SQLCMD) = ')htaPataD(

    --  SQLCMD variables have not been set, crash and burn!
    RAISERROR('This script must be called with the required SQLCMD variables!', 20, 1) with log

回复收藏 0 原文

仲春光 2024-10-04 02:45:12

您可以使用 try catch 语句完成您想要的事情。只需在 try 中访问变量之一，如果生成错误，请在 catch 中定义它们。

回复收藏 0 原文

扛刀软妹 2024-10-04 02:45:12

在菲利普的回答的基础上，我在脚本的开头创建了这个测试，以验证传入的要创建的数据库的有效名称：

DECLARE @valid_names VARCHAR(100) = 'DBPUB,DBPROD,DBPROD,DBEDITS,DBTEST,DBDEV'

IF CHARINDEX(UPPER('$(dbName)'), @valid_names) = 0
BEGIN
    PRINT 'This script must be called with a valid database name.'
    PRINT 'Valid names are: ' + @valid_names
    PRINT 'Example: sqlcmd -E -S DBSERVER1 -i create_database.sql -v dbName=DBPROD'
    SET NOEXEC ON
END
GO

PRINT 'Continuing script to create $(dbName)'

PS我发现没有方法可以确定变量是否在命令行调用中未定义因此，无法绕过“'dbName'脚本变量未定义”。如果未设置，则消息。

Building on Philip's answer, I created this test at the head of the script to verify the valid name of a database to be created was passed in:

DECLARE @valid_names VARCHAR(100) = 'DBPUB,DBPROD,DBPROD,DBEDITS,DBTEST,DBDEV'

IF CHARINDEX(UPPER('$(dbName)'), @valid_names) = 0
BEGIN
    PRINT 'This script must be called with a valid database name.'
    PRINT 'Valid names are: ' + @valid_names
    PRINT 'Example: sqlcmd -E -S DBSERVER1 -i create_database.sql -v dbName=DBPROD'
    SET NOEXEC ON
END
GO

PRINT 'Continuing script to create $(dbName)'

P.S. I've found no method to determine if a variable is undefined on the command-line call so, there's no getting around the "'dbName' scripting variable not defined." message if it's not set.

回复收藏 0 原文

清风挽心 2024-10-04 02:45:12

由于 sqlcmd 和 T-SQL 变量的访问方式不同，因此它们可以具有相同的名称，并使用一个变量来检查另一个变量是否存在。

DECLARE @DB VARCHAR(MAX);
SET @DB = '$(DB)'

IF CHARINDEX('
, @DB)=1
BEGIN
RAISERROR('DB var is missing!', 20, 1) WITH LOG
END
ELSE
PRINT(@DB)

RETURN;

Since sqlcmd and T-SQL variables are accessed differently, they can have the same name and use one to check for the existence of the other.

DECLARE @DB VARCHAR(MAX);
SET @DB = '$(DB)'

IF CHARINDEX('
, @DB)=1
BEGIN
RAISERROR('DB var is missing!', 20, 1) WITH LOG
END
ELSE
PRINT(@DB)

RETURN;

回复收藏 0 原文

~没有更多了~