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.

Pointillism

Answer 1

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

Mervin

This is the worst thing I've ever seen. Not the solution, mind you, but the need for it. – Means

Oh, there's lots as bad as this out there, and worse. – Mervin

This is not the answer to the question. This only checks if you're currently running the script in SQLCMD mode. – Melonie

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". – Mervin

This answer to a similar problem provides additional details related to this solution – Dever

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 – Procto

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 – Streak

Answer 2

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.

Tuchun

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. – Procto

Answer 3

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.

Exception

Answer 4

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;

Sasaki