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.
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
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.
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.
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;
