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