sql-server – sql脚本变量的默认值

SIMPLE版本 – 如果你愿意没有：错误退出/ 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

注意：

>由于T-sql变量不能跨批处理工作,因此无法启动另一个批处理(使用GO),因此无法在错误退出时切换到强大的错误处理.
因此,你必须在脚本的其余部分进行自己的错误处理 – 这是非常重要的;见SQL Server – stop or break execution of a SQL script
>如果删除：on on ignore以使脚本在常规模式下在SSMS中工作,请确保在使用sqlcmd.exe调用该脚本时未指定-b选项,因为这将阻止整个如果引用的脚本变量不存在,则运行脚本.
>通过有效地将脚本变量转换为常规T-sql变量,您无法在T-sql期望文字的位置使用该值,例如CREATE DATABASE语句中的数据库名称.
>如果未定义脚本变量,则会向stderr打印以下警告：
‘variablename’脚本变量未定义.

ROBUST版本 – 更麻烦,但支持：在错误退出时,这是可取的：

-- 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 + ']';

注意：

>以上工作在从sqlcmd.exe调用时和SSMS中以常规模式调用 – 假设您在脚本中不使用其他sqlCMD命令.遗憾的是,sqlCMD模式下的SSMS始终拒绝运行引用不存在的脚本变量的脚本.>需要使用SET COntexT_INFO,因为需要跨批处理边界传递值,这是使用T-sql变量无法完成的.需要多个批次才能切换回强大的错误处理.>上面的代码仅支持单个脚本变量,并且由于使用SET COntexT_INFO,其长度限制为128个字节= 64个Unicode字符;但是,可以想象使用其他解决方法,例如临时表.>通过有效地将脚本变量转换为常规T-sql变量,例如CREATE DATABASE语句中的数据库名称.>如果未定义脚本变量,则会向stderr打印以下警告：‘variablename’脚本变量未定义.