How not to include sql file content when publishing?

Question

I have a question that is very similar to an older question of mine Using variable in sql postdeployment build script? But now I don´t want to do an if/else and have the sql code in my published script. I don´t want to share information beetween customers.

So... here goes

• I have a database project in Visual Studio.
• I also have 2 customers with different needs

I know I can

• use post.deployment script to include content of sql files I need by setting a varible in Project.Properties."SQLCMD Variables".

And then use it like this in Script.PostDeployment.sql

:r ..\$(Customer)Setup.sql

Where I have 2 files

• Customer1Setup.sql
• Customer2Setup.sql

And then I set the SQLCMD Variable as $(Customer) "Customer1" or "Customer2" and only the one I had put in the SQLCMD is included.

But when I want to use the variable in the publishing window the default one overrides it

I know I can do this somewho in a build script but I want to know if I can use the current tools and just create Customer1.publish.xml and Customer2.publish.xml files so that I know that something that is for Customer1 does not go with the script if published to Customer2.

EDIT 1:

I created a public GitHub repository to try to address my problem. Hopefully somebody can take a look and edit it to work.

EDIT 2:

This is the result from my test project (same as my real one) where in every one of the 3 tests I have there result in the same. All the "stuff" in Customer2/Customer3 is included in the result. If this is supposed to work there is some switch or setting I´m missing in my setup (VS2015 database project)

SET NOEXEC ON
Print 'This print statement should not be in the publishing script if     Customer variable is not customer1'
print 'Customer1 stuff from Customer1.sql'
SET NOEXEC OFF

IF('$(Customer)' <> 'customer2')
SET NOEXEC ON
Print 'This print statement should not be in the publishing script if     Customer variable is not customer2'
print 'Customer2 stuff from Customer2.sql'
SET NOEXEC OFF

IF('$(Customer)' <> 'customer3')
SET NOEXEC ON
Print 'This print statement should not be in the publishing script if    Customer variable is not customer3'
print 'Customer3 stuff from Customer3.sql'
SET NOEXEC OFF

EDIT 3

So if I visualize this little better This is what I want to get

But this is what I get

Longs

Possible duplicate of Conditional logic in PostDeployment.sql script using SQLCMD – Roselinerosella

What is the problem with the conditional logic being part of the script? – Roselinerosella

Like I said in the chat : Sometimes the client gets the script to setup in their environment and then I don´t want any other customer-specific-things in that script. I´m just trying to make this automatic solution. But l´m thinking this could/should just be done in a build script (Cake?). But I kinda find it strange that this is not built into the tools. Maybe I should write an VS database extension? – Longs

Answer 1

The below won't work as per expectations:

:r ..\$(Customer)Setup.sql

Instead though, you could use the IF ELSE approach:

IF ('$(Customer)'='customer1')
BEGIN
    :r .\script_customer1.sql
END
IF ('$(Customer)'='customer2')
BEGIN
    :r .\script_customer2.sql
END

After a bit more research I found similar SO question. It turns out the above would not work in case you use GO in your scripts. So could use the below:

IF ('$(Customer)'<>'customer1')
    SET NOEXEC ON

:r .\script_customer1.sql
SET NOEXEC OFF

IF ('$(Customer)'<>'customer2')
    SET NOEXEC ON

:r .\script_customer2.sql
SET NOEXEC OFF

Roselinerosella

Ok awesome! I have searched and searched but have not found the the NOEXEC part! I will try this out rightaway. – Longs

Ok this is not working. Or at least I´m not doing it right. I added a link to a public GitHub repo where you can test this and hopefully update if possible. I´m counting on you Alex :-) – Longs

Both options from the answer have been tested. Have you tried just the "Generate Script" option and then examining it and possibly run in sql management studio? – Roselinerosella

Then I´m missing something else in the setup. I updated the question and here is a link to my GitHub (with explanations) github.com/sturlath/SQLCMDConditionalFiles. Can you get that to work? Would realy love to get this to work... – Longs

In your github, you comparing to the lowercase 'customer1', but in the publish files you use '<Value>Customer1</Value>'. Also the Customer1.sql has: print 'Customer1 stuff from Customer2.sql' inside. – Roselinerosella

The output from running Customer1.publish.xml is correct in my eyes: This print statement should not be in the publishing script if Customer variable is not customer1 Customer1 stuff from Customer2.sql Common stuff... Update complete. – Roselinerosella

And do you NOT see the content from the Customer2.sql and Customer3.sql in there? p.s updated github thanks. – Longs

Let us continue this discussion in chat. – Roselinerosella