Wednesday, January 16, 2013

SQLCMD - Calling :r in the middle of batch?

When we run external .SQL file from a SQL script, we can call the external script file by using :r command. For example, :r test.sql reads the test.sql and append it to the statement cache and run it. So this is pretty convenient command when one script calls multiple external SQL files. But can we use the :r command in the middle of a SQL batch? I recently got some trouble of using :r command in the middle of a batch. Especially if the external script file has multiple batches separated by GOs, using :r in the middle seems almost impossible.

Let us take a look at a simplified example. I have 3 branches in terms of control flow.
(1) First if [MainControl] table does not have Stop sign, we go to main script which is CREATE DATABASE MyDB statement.
(2) If [MainControl] table has Stop sign and [SubControl] has [RunScript1_And_Exit] sign, we need to run external Script1.sql file and exit immediately.
(3) If [MainControl] table has Stop sign and [SubControl] does not have [RunScript1_And_Exit] sign, we exit immediately.
So we probably bring up this kind of solution as you see below. And of course, please note this is SQLCMD mode script.

IF EXISTS (SELECT * FROM [MainControl] WHERE FLAG = 'Stop') 
 IF EXISTS (SELECT * FROM [SubControl] 
            WHERE FLAG = 'RunScript1_And_Exit') 
  :r Script1.sql
 ELSE
  RAISERROR('Stop. Exit now.', 20, -1) WITH LOG;   
GO

CREATE DATABASE MyDB
GO
-- MORE SCRIPTS HERE
--

And sure it did not work. Calling Script1.sql in the middle of a batch overrode existing control flow and all messed up thereafter. The solution that I bring up is (1) use :r command in separate single batch (2) and build control flow around :r command. I used temporary table since it can be used in multiple batches. (3) Use SET NOEXEC ON/OFF to control :r execution.

CREATE TABLE #RunScript1_Exit(flag BIT) 

IF EXISTS (SELECT * FROM [MainControl] WHERE FLAG = 'Stop') 
 IF EXISTS (SELECT * FROM [SubControl] 
              WHERE FLAG = 'RunScript1_And_Exit') 
  INSERT #RunScript1_Exit VALUES (1)
 ELSE
  RAISERROR('Stop. Exit now.', 20, -1) WITH LOG;   
GO

IF NOT EXISTS (SELECT * FROM #RunScript1_Exit)
  SET NOEXEC ON
GO
:r Script1.sql
GO   
SET NOEXEC OFF
GO
IF EXISTS (SELECT * FROM #RunScript1_Exit)
BEGIN
   RAISERROR('Ran Script1.sql and now Exit.', 20, -1) WITH LOG;   
END   
GO

CREATE DATABASE MyDB
GO

-- MORE SCRIPTS HERE
--


SET NOEXEC ON ignores all the SQL statements until it meets SET NOEXEC OFF. So by using this, we can control whether we can run :r command or not. And also RAISERROR WITH LOG was used to exit the script. RAISERROR with sevirity 20 terminates connection and exits immediatly.

No comments:

Post a Comment