sql scripting variable default value

Question

I have a script file e.g. test.sql. I want to call this from another script, say caller.sql, in sqlcmd mode using :r test.sql. This works fine, but I want to use a scripting variable in test.sql. When I call test.sql from caller.sql I can set the scripting variable and all is well. However, I want to use a default value for the scripting value so that if the caller does not set the variable, or if I run test.sql directly (not from caller.sql) then the scripting variable defaults to a set value.

I have tried things such as

begin try
 select '$(grip)'
 select 'grip value was found'
end try
begin catch
 select 'grip value was missing'
end catch

but I just get the following message: A fatal scripting error occurred. Variable grip is not defined.

What do I need in test.sql so that it can cope with 'grip' either being passed by the caller or not? I am using MS SQL 2005

user_2216359 · Answer

Setting the Context is an original and interesting idea. When I read the issues you faced parsing the VarBinary I thought perhaps a temp table would answer the need. After working on it a bit (and then remembering your comment that SSMS ALWAYS errors :( when the scripting variable is undefined) I got this version working. I generally don't like Name-Value solutions but performance is not really and issue here and it provides a great deal of flexibility at the cost of the boiler plate required to define and test each optional parameter. I tested on SQL2008R2 using SSMS 17.

Set NoCount On;
If Object_Id('tempdb..#hold', 'U') Is Not Null Drop Table #hold;
Go
Create Table #hold(theKey NVARCHAR(128) Not Null, theValue NVARCHAR(128) Not Null);
Insert #hold(theKey, theValue) Values (N'Type', N'Full');
Select theValue From #hold Where theKey = N'Type'

:on error ignore
    Declare @theValue   NVARCHAR(128) = N'$(BType)';
    If @theValue <> N'$' + N'(BType)'   -- a value was passed in
        Update #hold Set theValue = @theValue Where theKey = N'Type';
Go
:on error exit

Select theValue From #hold Where theKey = N'Type'
Return;
-- sqlcmd -S myServer -E -i thePath	heScript.sql -v BType = "diff"

sql scripting variable default value

6 Answers