Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

share|improve this question

6 Answers 6

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.
share|improve this answer

Perhaps one of these 3 options:

  • via command line option v
  • through the :SETVAR command described later in this chapter
  • by defining an environment variable prior to running SQLCMD.

Using v option

In your script: SELECT $(foo) FROM $(bar)
Usage: C:>SQLCMD i c:\someScript.sql -v foo="CustomerName" bar="Customer"

Using setvar

:setvar foo CustomerName
:setvar bar Customer

From
http://www.sqlcmd.org/sqlcmd-scripting-variables/

share|improve this answer
    
thanks for the suggestions, but none of these appear to address the need for a default value (within test.sql) that can be overridden by another value when called by caller.sql (I can't get it to work). The :setvar option looks good, but the setvar in test.sql takes precedence over anything set within caller.sql –  DEH Sep 20 '10 at 11:56

Retrieving the variable and assign to a variable, if it falls through the SET statement then it is assigned. The catch will handle the default values. This works for me on SQL Server 2005. I have noticed that this always says that it failed when I run it in SQL Management Studio even though I think it works. When I run this via command-line using sqlcmd it works without any error messages.

BEGIN TRY 
    DECLARE @bogusVar VARCHAR(64);
    SET @bogusVar = '' + $(envVar);
    PRINT 'Using values passed from sqlcmd';
END TRY
BEGIN CATCH
    PRINT 'Using default values for script'
    :setvar envVar 'DefaultValue'
END CATCH;
share|improve this answer

What wound up being remotely practical for me is setting a system environment variable to the default and then overriding that value where desired with SQLCMD -v.

share|improve this answer
up vote -1 down vote accepted

Couldn't find any way of doing this

share|improve this answer

If you're running SQLCMD from a Windows Command promt, add ">2 nul" at the end of your SQLCMD statement. That will eliminate the SQLCMD complaints.

Example: sqlcmd -S DatabaseServer -E -d MyDatabase -i MyScript -v Variable1=Value1 2> nul

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.