If condition in SQLCMD script in sql database project

Question

I created Sql Server Database Project in Visual Studio and i wonder if there is possible to use something like this

pre deployment script

:setvar Environment Production

post deployment script

IF '$(Environment)' = 'Production'
BEGIN
    ALTER VIEW [dbo].[vwTable]
    AS
        SELECT Col1, Col2 FROM Production.database.dbo.Table1
    GO
END
ELSE
BEGIN
    ALTER VIEW [dbo].[vwTable]
    AS
        SELECT Col2, Col3, Col4 FROM LinkedServer.Development.database.dbo.Table2
    GO
END

error is : Alter view must be the only statement in the batch.

or simplier version

ALTER VIEW [dbo].[vwTable]
AS
IF '$(Environment)' = 'Production'
    SELECT Col1, Col2 FROM Production.database.dbo.Table1
ELSE
    SELECT Col2, Col3, Col4 FROM LinkedServer.Development.database.dbo.Table2
GO

error is : Incorrect syntax near IF. Expecting Select or With

any idea, what is your approach ?

Answer 1

There is a way to do this, but that info must first be prefaced with:

Don't     do     this!!

Your object definitions really need to be the same across all environments. Meta-data (as in config data) can be different, but not object definitions. Else you are setting yourself (not to mention the next unlucky person to take this over) up for failure. It is unmaintainable. There are differences in performance, functionality, and security between using a Linked Server and not using one. In your current setup, any QA done against this code is invalid as it isn't even close to testing the same thing in each environment.

BUT, it is understandable that there are environment-specific differences that need to be dealt with. In which case, you should have a different Linked Server definition in each environment. Don't need the Linked Server in one or more environments? That's fine, just create a "loop back" Linked Server that points at the Instance that it is currently residing in.

---

That said, you just need to bury definitions for objects requiring their own batch in an EXEC, such as the following:

IF ('$(Environment)' = 'Production')
BEGIN
    EXEC(N'ALTER VIEW [dbo].[vwTable]
           AS
           SELECT Col1, Col2
           FROM Production.database.dbo.Table1;');
END
ELSE
BEGIN
    EXEC(N'ALTER VIEW [dbo].[vwTable]
           AS
           SELECT Col2, Col3, Col4
           FROM LinkedServer.Development.database.dbo.Table2;');
END;