Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010?

I'm using SQL Server Data Tools in Visual Studio 2010 to work on an experimental SQL Server Express database. The eventual destination if things work well would be an enterprise SQL Server platform. I have both a 2008 instance on one box and a 2012 instance on another, because my company is in the process of migrating from 2008 to 2012 for the many enterprise databases as well.

In other programming languages I have used, preprocessor directives make it easy to conditionally compile parts of a code base. The most common uses for this are to have different code for different platforms in restricted sections or to excluded debugging output code from release builds.

Both of these could be very helpful in some store procedures I'm working on. Is there anything like this available? I know I can use sqlcmd variables to swap out specific values during deployment, but I can't figure how to use that to include or exclude subsequent chunks of code.

Example:

#IF $(DebugVersion) = 'True'
    -- A bunch of useful PRINTs and what not
#ELSE
    SET NOCOUNT ON
#ENDIF

#IF $(SSVersion) = '2012'
    SET @pretty_date = FORMAT(@some_date, 'dddd, MMM dd, yyyy')
#ELSE
    SET @pretty_date = CAST(@some_date AS nvarchar(12))
#ENDIF
share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.