(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