HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Skip sections that have GO

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
havethatskipsections

Problem

I'm working on a script I can run on new servers to do some initial configuration. I would like to be able to skip some sections but the problem I'm running into is the "GO" that's required. Because of the GO I can't wrap my sections of code in IF statements. For example

:setvar EnableDBMail 0

IF ($(EnableDBMail) = 1 )
BEGIN
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

END
....etc


The problem is this won't work because you have to use GO. Is there another way of doing this? I've tried searching but have yet to find anything.

Solution

Whether or not you are using SQLCMD mode, you can always avoid the need for a GO (which enforces batch separation) by wrapping the calls in an EXEC(). For example:

IF ($(EnableDBMail) = 1 )
BEGIN
EXEC('sp_configure ''show advanced options'', 1;');
EXEC('RECONFIGURE;');
EXEC('sp_configure ''Database Mail XPs'', 1;');
EXEC('RECONFIGURE;');
END;


This also works when creating objects that need to be the only CREATE in the batch, such as: stored procedures, functions, triggers, views, etc. The following is a single batch and can be wrapped in an IF or whatever else:

CREATE TABLE dbo.MyTable
(
Col1 NVARCHAR(256) NOT NULL
);

EXEC('
CREATE PROCEDURE dbo.Proc1
(
@Param1 INT
)
AS
SET NOCOUNT ON;

SELECT field
FROM table;
');

EXEC('ALTER TABLE dbo.MyTable ADD Col2 DATETIME NULL;');

Context

StackExchange Database Administrators Q#84708, answer score: 6

Revisions (0)

No revisions yet.