1

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 ?

Improve this question
6
  • 1
    @Mufflix, You cannot use IF condition in SQL Server VIEW. Commented Mar 13, 2017 at 12:32
  • @Venu i know, but how to use condition with SQLCMD ? because i have different view for production and for development. Commented Mar 13, 2017 at 12:34
  • are you saying that you have SQL Server VIEW in SQLCMD mode, which has to decide which statement to execute based on the environment? I don't believe that. Commented Mar 13, 2017 at 12:40
  • @Venu why not, how else to do it ? Commented Mar 13, 2017 at 12:50
  • 1
    You cannot solve this with a view. you may need to create procedure instead. I added details as an answer below. Commented Mar 13, 2017 at 13:05

4 Answers 4

Reset to default
3

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

Don't   Danger Will Robinson!   do   Danger Will Robinson!   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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Can't you write it as:

ALTER VIEW [dbo].[vwTable] AS SELECT Col1, Col2 FROM $(Environment).database.dbo.Table1 GO

You should be able to control the different columns too as I believe the command variables just implant text into the script before it runs.

Improve this answer

1 Comment

I am using this technique elsewhere but i have problem with omitting the LinkedServer address, i updated the question. Also i think this is a workaround rather then solution, because it can be more complex sql query in the future, but thank you !
1

You should use Procedure instead of View with a parameter for passing the environment. An ALTER VIEW statement must be the first statement of the batch, so you cannot use it in this way. Even if you somehow execute the ALTER VIEW statement, you will not be able to create a VIEW. As a view is a plainT-SQLand cannot understandSQLCMD` commands/variables.

The procedure may look like the one below.

CREATE PROCEDURE [dbo].[usp_Results_Based_On_Environment] (@pEnvironment varchar(50))
AS
BEGIN
    IF @pEnvironment = 'Production'
        SELECT Col1, Col2 FROM Production.database.dbo.Table1
    ELSE
        SELECT Col2, Col3, Col4 FROM LinkedServer.Development.database.dbo.Table2
END
GO
Improve this answer

1 Comment

I only need to update existing deploy script which will execute SQL queries based on the environment. I dont want to change my database schema. The sql server does not know the development server and 'create procedure query' will fail anyway i believe.
0

Its not exactly what i wanted to do but it also helped me to solve the issue. It is possible to include files, but i need manually comment/uncomment specific lines before publish.

:r .\included\production.views.script.sql
--:r .\included\development.views.script.sql

also the file can contain many GO statements.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.