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

Question

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.

score 18 · Accepted Answer · answered Oct 12 '10 at 13:48

18

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

answered Oct 12 '10 at 13:48

Philip Kelley

39,426
11
57
92

5

This is the worst thing I've ever seen. Not the solution, mind you, but the need for it. – FLGMwt Jan 15 '15 at 15:37
1

Oh, there's lots as bad as this out there, and worse. – Philip Kelley Jan 15 '15 at 18:40
This is not the answer to the question. This only checks if you're currently running the script in SQLCMD mode. – kjbartel Feb 05 '16 at 05:26
This checks whether the variable has been defined or not, where the variable might have been "from within" SQLCMD mode, or from actually being called by SQLCMD. So, while it does not say whether or not the call originated from SQLCMD, it *does* address their desire to "...allow the script to be run both from command line and inside of SSMS". – Philip Kelley Feb 05 '16 at 15:10
This [answer](http://stackoverflow.com/a/8485303/1905436) to a similar problem provides additional details related to this solution – Gustavo Russo Sep 21 '16 at 22:35
1

This only checks if you're currently running the script in SQLCMD mode. If you are and the variable is not defined (as per the OP's question), it bombs out (Variable Test_SQLCMD is not defined), and never executes the RAISERROR command – Reversed Engineer Mar 08 '18 at 12:55
The OP asked "I want to allow the script to be run both from command line and inside of SSMS." It does work for that fine with a bit of a tweak just declare your value for SSMS with a standard sql var ` declare @datapath varchar(10) = 'my ssms defautl value' - - then set if NOT a sql command var SET @Test_SQLCMD = '$(DataPath)' IF not reverse(@Test_SQLCMD) = ')htaPataD($' SET @datapath = '$(DataPath)' ` It works because SSMS doesn't complain when the env var is in a string but is still substituted by SQLCMD IMO - Very clever - thanks – stuartm9999 Nov 04 '22 at 16:27

score 0 · Answer 2 · answered May 01 '14 at 18:46

0

You can accomplish what you want with a try catch statement. Just access one of your variables in the try, if that generates an error, define them in the catch.

answered May 01 '14 at 18:46

Lew

1

3

SQL TRY...CATCH does not catch SQLCMD errors. The SQLCMD processor works before the SQL statements are executed, so the error occurs anyway, before the TRY...CATCH has executed. – Reversed Engineer Mar 08 '18 at 13:21

Erik Anderson · Answer 3 · 2022-08-01T17:29:01.397

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.

score 0 · Answer 4 · answered Oct 21 '22 at 14:06

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;

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

4 Answers4