CREATE BLOG dbo.SqlServer

I haven't really seen people using this but somehow I came across it last month. I honestly can't remember if it's an original though or whether I accidently stole it from someone else's code but, either way, it's a way cool trick :)

From time to time, I need to run some setup/modification scripts in my database but, unless I'm in that database, I don't want the code to run. For example:

USE [learnsqlserver.com]
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')
Go

Yes, yes, yes the code is silly but it works for what I'm trying to show you here. Here's the problem with the above code: if the database [learnsqlserver.com] is not available for any reason, the code continues processing and will, depending on permissions, create a new table in the current database and dump lots of rows into it.

What I wanted was a way to write my SQL scripts so that, if [learnsqlserver.com] was unavailable, it would not execute any more of the script. It turns out that it's actually kind of easy to do this but you have to be wary of how one connection-level setting works.

SET NOEXEC

The key to doing this is to use the command SET NOEXEC . If it is turned on, SQL Server will parse the code but not execute it (which is ideal for the above situation).

Here's a revised version of our code:
USE [learnsqlserver.com]
GO
IF DB_NAME() != 'learnsqlserver.com'
SET NOEXEC ON
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')
GO

Problem solved! If the database is anything other than [learnsqlserver.com] then your following code will not execute. One helpful idea is that SET NOEXEC ON honors SET NOEXEC OFF so, even if SET NOEXEC is turned on, you can still turn it off so that you can run other code (example below):

USE [learnsqlserver.com]
GO
IF DB_NAME() != 'learnsqlserver.com'
SET NOEXEC ON
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
SET NOEXEC OFF
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')

I will offer one suggestion: once you SET NOEXEC ON, it remains ON until you explicitly set it OFF (on a per-connection basis). I would suggest that, if you plan on using this in your scripts, that you prefix all scripts with SET NOEXEC OFF to prevent any future runs of the script from being affected by previous settings. Here's what I mean:

SET NOEXEC OFF -- to prevent a future execution from not running script
USE [learnsqlserver.com]
GO
IF DB_NAME() != 'learnsqlserver.com'
SET NOEXEC ON
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')
GO

Hope this helps you like it has me - it's really been a big help :)

# posted by Scott Whigham @ 3:44 PM