Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to tell if a variable (-v) is defined on command line (SQLCMD)

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.

like image 764
Gerhard Weiss Avatar asked Oct 12 '10 13:10

Gerhard Weiss


People also ask

How do I declare a variable in SQLCMD?

Scripting variables can be defined explicitly by using the setvar command, or implicitly by using the sqlcmd -v option.

How can I tell if SQLCMD is enabled?

If you need SQLCMD mode to be the default, go to Tools -> Options -> Query Execution -> SQL Server -> General and check the "By default, open new queries in SQLCMD mode".

What is the purpose of using the SQLCMD command?

The sqlcmd Utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in SQLCMD mode in SQL Server Management Studio, and in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job.


1 Answers

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
like image 130
Philip Kelley Avatar answered Sep 20 '22 17:09

Philip Kelley