I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been the SQLCMD utility. I am going to demonstrate the use of include files. The following is the complete call syntax for SQLCMD.
:r is a SQLCMD command that parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.
A simple example:
In SQLQuery1.sql I have the following:
In SQLQuery2.sql I can then reference SQL file 1 as follows:
My results are:
ProductID Name ProductNumber MakeFlag
1 Adjustable Race AR-5381 0
2 Bearing Ball BA-8327 0
3 BB Ball Bearing BE-2349 1
4 Headset Ball Bearings BE-2908 0
316 Blade BL-2036 1
This may not seem like a big deal, but consider the following scenario. I have a large bat file of SQL that I process in jobs or in SSIS packages. This bat file is doing and setting the same variables over and over again, so I can create include files that declare my variables, and another include file that sets them.
My first file “c:\DeclareVariables.sql” will always change me to the correct DB, set no count on and declare my variables. This becomes good anchor file for any pre-processing that I may want to do.
“c:\SetVariables.sql” is where is set my variables. You notice that the SQL variable @Status is set to a SQLCMD variable of the same name “Status”. This allows me to control that from my batch sql file. I am trying to eliminate any hard coded references in my global files.
And finally the batch file “c:\SQLBatch.sql”. The first two lines set my include files to variables. This allows for a consistent naming through all my files and allows me to change the files without having to change the batch code. This is good for testing and portability. The next line is where I set the SQLCMD “Status” variable. This variable is then set in the SetVariables.sql file to the TSQL variable @Status that is passed to the function ufnGetDocumentStatusText.
Now all I have to do it execute the file “c:\SQLBatch.sql”. If you are familiar with ASP include files this should be easy to understand.
The more you use this utility the more you will enjoy it. I will be posting more topics on SQLCMD in the future. Stay Tuned!