SQL CLR conditional compilation based on SQL Server version

Question

I'm been using Visual Studio conditional compilation based on the .Net Framework for many of my projects.

But now I'm encountering a need to conditionally compile in a Visual Studio SQL CLR project based on the selected target SQL Server version.

Is there a way to do that? I need a way to optionally include in the compiled DLL certain .sql files (stored procedures) and C# .cs files (SQL CLR stored procedures)

Thanks for your help!

Edit:

I found a solution for the .cs portion of the SQLCLR project. I followed the same steps for detecting .Net Framework as per this Stack Overflow question and then added to the VersionSpecificSymbols.Common.prop file lines like these:

<DefineConstants Condition="$(DSP.Contains('100')) == True">$(DefineConstants);SQL2008</DefineConstants>

I'm still looking for a way to "filter out" stored procedures based on SQL Server version

score 1 · Answer 1 · answered Dec 17 '17 at 00:19

Since there are no additional answers provided, I'm including my partial solution for the .cs portion of the SQLCLR project:

I followed the same steps for detecting .Net Framework as per this Stack Overflow question and then added to the VersionSpecificSymbols.Common.prop file lines like these:

<DefineConstants Condition="$(DSP.Contains('100')) == True">$(DefineConstants);SQL2008</DefineConstants>

<DefineConstants Condition="$(DSP.Contains('110')) == True">$(DefineConstants);SQL2012</DefineConstants>

<DefineConstants Condition="$(DSP.Contains('120')) == True">$(DefineConstants);SQL2014</DefineConstants>

<DefineConstants Condition="$(DSP.Contains('130')) == True">$(DefineConstants);SQL2016</DefineConstants>

score 0 · Answer 2 · answered Dec 14 '17 at 10:17

Is there a way to do that?

To my knowledge, I am afraid you could not do that. As we know, there is a few ways to get the version of SQL Server:

How to check SQL Server version

But we could not get SQL Server version from the project file .sqlproj via MSBuild. So we could not use the condition for the .sql files (stored procedures) and C# .cs files (SQL CLR stored procedures) directly, like:

  <ItemGroup>
    <Compile Include="SqlStoredProcedure1.cs" Condition=" '$(sqlversion)' == 'sql server 2012' " />
  </ItemGroup>

As a workaround, you can define a new configuration:

For example, SqlServer2012, then set this condition to the .sql files (stored procedures) and C# .cs files (SQL CLR stored procedures):

  <ItemGroup>
    <Build Include="Procedure1.sql" Condition=" '$(Configuration)' == 'SqlServer2012' "/>
  </ItemGroup>

  <ItemGroup>
    <Compile Include="SqlStoredProcedure1.cs" Condition=" '$(Configuration)' == 'SqlServer2012' " />
  </ItemGroup>

In this case, those two file only included when you configuration is selected to SqlServer2012.

Note: The limitation of this approach is that you need to know SQL Server version in advance.

Hope this helps.

I just edited my question with a partial solution, but your idea for using different configurations is something I did not consider. The only problem I see is the need to specify the .sql files with each configuration. I was hoping more for a pre-deployment script action. — SQLHTTP, Dec 14 '17 at 16:22
@SQLHTTP, That is OK, you can share your solution with a answer and mark it, so it is more easily for other communities to found the solution. — Leo Liu, Dec 14 '17 at 16:49

SQL CLR conditional compilation based on SQL Server version

2 Answers2