Conditional compilation of SQL Server stored procedure

I'm not aware of this being possible with SSDT unfortunately.

Depending on how big the project is and how many procedures you intend to enhance with 2012 goodies, it may be manageable with Composite Projects.

SSDT can combine a database project with one or more referenced database projects or dacpacs to describe a single composite database schema. Using a composite project allows a large database to be broken down into more manageable chunks, allows different people or teams to have responsibility for different parts of the overall schema, and enables reuse of database object definitions in multiple databases.

The notion would be to have a base project, containing the common object definitions and version specific projects for procedures that used new features. The 2012 project would reference the base project and a compile/build would combine objects from both.

The PITA would be that you can't override an object in the base project with an object in a composite, so you would have to maintain base, 2008 & 2012 projects. When you wanted a 2012 version of a particular procedure, you would have to remove it from base and create a version in both 2008 & 2012 projects.

I have had an open discussion on MSDN about this need. Have not made much progress. The ideal situation would allow you to flag db objects as "inheritable" in base ssdt projects so that other projects that reference the base project or DAC won't complain of the duplicate object, and would only create the base object or "stub" if it did not exist. This would allow you to have "layers" of database models. See my post on msdn Extending SSDT Composite Solutions with Overriden Stored Procedures

I achieved something close to what is being asked by using prebuild events. In my case I wanted different users and logins to be included in a deployment according to which configuration was being built.

To do this I created a .sql file for each build config, for each user. I flagged these as Build Action 'None': (I put these in a subfolder of the project)

• Debug_SomeUser.sql
• Release_SomeUser.sql

I then created an empty file SomeUser.sql (with Build Action = 'Build').

In the Pre-build event command line :

Copy $(ProjectDir)subfolder\$(Configuration)_Someuser.sql $(ProjectDir)Someuser.sql

It was a nuisance that a team of coders was constantly creating conflicts in git, so I also added to the pre-build command line

git update-index --assume-unchanged $(ProjectDir)Someuser.sql 

It was fairly fiddly to get this working as I had a much larger number of users and configs to consider, but it works.