Skip sections that have GO

Question

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.

Answer 1

I did some searching myself, since I noticed that you're running this query in SQLCMD mode, and I didn't know if it would be any different. Did a quick Google search, and found: https://stackoverflow.com/questions/3988199/how-to-use-sp-configure-in-another-stored-procedure

Basically, "GO" is just the batch separator within SSMS, and you should be able to run those commands using "EXEC". "GO" tells the server that you've ended the TSQL batch, and not continue running anything else. Since you need to follow up sp_reconfigure with RECONFIGURE, putting "GO" between them breaks the code block you specified with "BEGIN" and "END". Ran this through a dev environment:

:setvar EnableDBMail 1 -- did this to make sure it works

IF ($(EnableDBMail) = 1)
BEGIN
EXEC sp_reconfigure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'database Mail XPs', 1;
RECONFIGURE;

END

Hope this helps!

Answer 2

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;');