Thursday, 6 November 2008

Variables in Microsoft SQLServer scripts

In Oracle you can create a script that takes configurable variables using the '&&var_name' syntax.

In SQLserver it is slightly different. The variables are defined using the syntax
:setvar var_name var_value
Then they are used using the syntax
CREATE TABLE $(var_name)
I have about ten scripts that all want the same selection of variables. First off I
duplicated the 'setvar' commands in each script but that is rubbish.

A better solution is to pull all these out into a separate file.

And then in the script you can refer to this file with the syntax
:r C:/TEMP/
Furthermore I could have a master script that calls all the scripts using the syntax:
:r C:/TEMP/
:r C:/TEMP/script_1.sql

:r C:/TEMP/script_2.sql
The one major gotcha is that these have to be run in SQLCMD mode.
To run the scripts from within SQL Server Manager Studio click on the SQLCMD Mode button. That's the one with the red exclamation mark before you execute the script.

There are more MS SQL tips here.

No comments: