(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