SQLCLR Stored Procedures with Optional Parameters / Default Values
Now that C # supports optional parameters, is there a way to write SQL CLR stored procedures so that publishing from Visual Studio creates stored procedures on the SQL server using the optional parameters that are defined in C #?
The only way to do this in the past was to manually write the wrapper functions:
Default / Optional Parameter Values for .NET Stored Procedures in SQL Server 2005
I would rather avoid this as it requires writing the interface twice and also keeping the two interfaces when something changes.
Example:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void help(string FunctionName = "")
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("help> " + FunctionName + "<");
}
Posting from VS2013 gives (in the generated posting script):
CREATE PROCEDURE [dbo].[help] @FunctionName [nvarchar](MAX)
AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];
Run:
exec help
Procedure or function 'help' expects parameter '@FunctionName', which was not supplied.
However, if you manually create the procedure creation like this:
drop procedure [help]
CREATE PROCEDURE [dbo].[help]
( @FunctionName nvarchar(100) = null)
AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];
exec help
help> <
.... it now works as expected.
Visual Studio has all the information it needs to publish parameters as optional now that C # supports them, so either I'm doing something wrong or I just haven't updated the publish implementation to recognize optional parameters.
source to share
At this time, it is not possible to specify the default SSDT (SQL Server Data Tools) publishing parameter values for parameters. Yes, this is frustrating as it requires an extra step to parse and "fix" the generated .sql script, or add a post-deploy script that follows the instructions ALTER {object}
to add missing details (including other functionality, such as the option WITH RETURNS NULL ON NULL INPUT
for scalar functions, which very convenient).
I created a Connect clause to address this flaw: SSDT - Supports default T-SQL parameters for SQLCLR objects using the SqlFacet attribute when publishing and generating SQL scripts
Two notes related to the ability to specify default parameter values:
- They will not apply to T-SQL parameters of LOB types -
NVARCHAR(MAX)
,VARBINARY(MAX)
andXML
- because these data types do not support default values in SQLCLR objects. This is another disappointment and I registered the following Connect proposal to address this flaw: Maintain default parameter values for NVARCHAR (MAX), VARBINARY (MAX), and XML types in SQLCLR objects - They will (and should) be treated as an additional property of the SqlFacet attribute . The reason they shouldn't be derived from C # method parameters is because default parameters don't work the same between C # and T-SQL. Not only is there the LOB type constraint mentioned above, but in T-SQL, the position of any parameter that has a default value is irrelevant, and parameters with default values can be followed by the required parameters (i.e. a given default value). Conversely, in C #, optional parameters must be at the end of the parameter list and cannot follow the required parameters. As the MSDN page states Named and Optional Arguments :
Additional parameters are defined at the end of the parameter list after any required parameters. If the caller provides an argument for any of several optional parameters, it must supply arguments for all previous optional parameters.
- I'm currently working on marking these defaults as well as other missing attributes
CREATE PROCEDURE
andCREATE FUNCTION
(i.e.EXECUTE AS
,RETURNS NULL ON NULL INPUT
etc.), but it's a bit tricky to get it to work as easily as possible in the SSDT posting process, so ETA will not be enabled when this is completed. Once that's done, I'll update this answer with a download link.
UPDATE:
In the end, it might be easiest to manually handle the T-SQL wrapper object. You can force Visual Studio to generate the Collection and any required DB parameter instructions and then do CREATE PROCEDURE
, CREATE FUNCTION
etc. On your own by doing:
- In Project Properties -> Project Settings , check the Generate script (.sql file) box .
- In Project Properties -> SQLCLR, uncheck Create DDL .
- Add New Item -> SQL Server -> User Scripts -> After Script is deployed and add
CREATE
script statements to it .
source to share