patternsqlMinor
Skip sections that have GO
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
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.
: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
....etcThe 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
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
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.