There is a LIMITED workaround (I've only tested it on SS2008R2):
SIMPLE version - if you're willing to live without :on error exit / sqlcmd.exe -b:
:on error ignore -- Ensures that sqlcmd.exe will not fail when referencing an undefined scripting variable. Remove this if you want your script to work in SSMS in regular mode, too.
Declare @valueOrDefault as nvarchar(max)= N'$(value)';
if @valueOrDefault = N'$' + N'(value)' set @valueOrDefault = N'default value'; -- Test if there is a value and, if not, assign a default; note the splitting of the reference string to avoid expansion.
-- use @valueOrDefault from now on
Note:
- Since T-SQL variables don't work across batches, you cannot start another batch (with GO) and so cannot switch to robust error handling with
:on error exit.
Therefore, you have to do your own error handling in the remainder of the script - which is non-trivial; see SQL Server - stop or break execution of a SQL script
- If you remove the
:on error ignore in order to make the script work in SSMS in regular mode, be sure that when you invoke that script with sqlcmd.exe that you do NOT specify the -b option, as that will prevent the entire script from running if the referenced scripting variable does not exist.
- By effectively turning the scripting variable into a regular T-SQL variable, you cannot use the value in places where T-SQL expects literals, such as the name of a database in a CREATE DATABASE statement.
- If the scripting variable is not defined, the following warning is printed to stderr:
'variableName' scripting variable not defined.
ROBUST version - much more cumbersome, but supports :on error exit, which is advisable:
-- Store the default value in the context info (session-level storage accessible across batches that holds up to 128 bytes).
declare @binDefaultValue varbinary(128)= CAST(N'default value' AS varbinary(128));
set CONTEXT_INFO @binDefaultValue;
go -- Make the set CONTEXT_INFO statement take effect.
-- If the scripting variable has a value, store ITS value in the context info instead.
:on error ignore -- Temporarily ignore errors so that accessing a non-existent scripting variable doesn't abort the entire script.
declare @value as nvarchar(max) = N'$(value)'; -- Try to access the scripting variable; thanks to :on error ignore this will only give a warning.
if @value <> N'$' + N'(value)' -- Test if there is a value; note the splitting of the reference string to avoid expansion.
begin
-- We have a scripting-variable value: Store it in the context info (replacing the default value).
declare @binValue as varbinary(128) = cast(@value as varbinary(128));
set CONTEXT_INFO @binValue;
end
go -- End batch here, so we can switch back to :on error exit (requires a new batch).
:on error exit -- New batch: switch back to robust error handling.
-- End the batch here, so that SSMS in *regular* mode - which will fail on the line above - continues processing below.
-- Note that when run by sqlcmd.exe the subsequent batches will inherit :on error exit.
go
-- Retrieve the value or default value from the context info...
declare @valueOrDefault as nvarchar(max) = convert(nvarchar(max), CONTEXT_INFO(), 0);
-- ... and remove trailing null characters. ?? Is there an easier way to do this?
declare @pos as int = 0;
while @pos < LEN(@valueOrDefault)
begin
set @pos=@pos+1
if UNICODE(substring(@valueOrDefault, @pos, 1)) = 0 break;
end
if @pos > 0 set @valueOrDefault = left(@valueOrDefault, @pos - 1);
-- @valueOrDefault now contains the scripting-variable value or default value.
print 'Value or default value: [' + @valueOrDefault + ']';
Note:
- The above works both when invoked from sqlcmd.exe and in SSMS in regular mode - assuming you use no other SQLCMD commands in the script. Sadly, SSMS in SQLCMD mode always refuses to run a script that references a non-existent scripting variable.
- The use of
SET CONTEXT_INFO is required, because values need to be passed across batch boundaries, which can't be done with T-SQL variables. Multiple batches are needed to switch back to robust error handling.
- The code above only supports a single scripting variable, and, due to use of
SET CONTEXT_INFO, its length is limited to 128 bytes = 64 Unicode characters; it's conceivable to use other workarounds, though, such as temporary tables.
- By effectively turning the scripting variable into a regular T-SQL variable, you cannot use the value in places where T-SQL expects literals, such as the name of a database in a
CREATE DATABASE statement.
- If the scripting variable is not defined, the following warning is printed to stderr:
'variableName' scripting variable not defined.
