CODEWITHSUNDEEP
c#sql-serversqlclr
tbone
tbone

Reputation: 5865

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 will create the stored procedures in SQL server using optional parameters where defined in C#?

The only way to do it in the past was by manually writing wrapper functions:
Default parameter values/optional parameters for .NET stored procedures in SQL Server 2005

I'd rather avoid that as it requires writing the interface twice, as well as maintaining two interfaces when things change.

Example: 

[Microsoft.SqlServer.Server.SqlProcedure]
public static void help(string FunctionName = "")
{
    SqlPipe pipe = SqlContext.Pipe;
    pipe.Send("help> " + FunctionName + "<");
}

Publishing from VS2013 yields (in the generated publish script):

CREATE PROCEDURE [dbo].[help] @FunctionName [nvarchar](MAX)
AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];

Running it:

exec help
Procedure or function 'help' expects parameter '@FunctionName', which was not supplied.

However, if you manually write the procedure creation as so:

drop procedure [help]
CREATE PROCEDURE [dbo].[help] 
( @FunctionName nvarchar(100) = null)
AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];

exec help 
help> <

....it now runs 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 they simply haven't updated the publishing implementation to recognize optional parameters.

Upvotes: 3

Views: 4772

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

It is not currently possible to indicate to the SSDT (SQL Server Data Tools) publishing process default values for parameters. Yes, this is frustrating as it does require an additional step of either parsing and "fixing" the generated .sql script, or adding a post-deploy script that does the ALTER {object} statements to add the missing details (which include other things such as the WITH RETURNS NULL ON NULL INPUT option for scalar functions that is very handy).

I have created a Connect Suggestion to address this deficiency: SSDT - Support T-SQL parameter defaults for SQLCLR objects via the SqlFacet attribute when generating the publish and create SQL scripts

Two notes related to the possibility of specifying parameter default values:

  • They wouldn't apply to T-SQL parameters of LOB types -- NVARCHAR(MAX), VARBINARY(MAX), and XML -- as those datatypes do not support default values in SQLCLR objects. This is another frustration and I have logged the following Connect Suggestion to address this deficiency: Support default parameter values for NVARCHAR(MAX), VARBINARY(MAX), and XML types in SQLCLR objects
  • They would (and should) be handled as an additional property of the SqlFacet Attribute. The reason that they should not be derived from the C# method param defaults is that parameter defaults do not work the same between C# and T-SQL. Not only is there the LOB type restriction mentioned above, but in T-SQL the position of any parameter having a default is not relevant, and parameters with default values can be followed by required parameters (i.e. no default value specified). Conversely, in C# optional parameters must be at the end of the parameter list and cannot be followed by required parameters. As it states in the MSDN page for Named and Optional Arguments:

    Optional parameters are defined at the end of the parameter list, after any required parameters. If the caller provides an argument for any one of a succession of optional parameters, it must provide arguments for all preceding optional parameters.

  • I am currently working on a means of noting these default values, as well, hopefully the other missing attributes of CREATE PROCEDURE and CREATE FUNCTION (i.e. EXECUTE AS, RETURNS NULL ON NULL INPUT, etc), but it is a little tricky getting it to work as seamlessly as possible within the SSDT publishing process so no ETA on when that will be completed. Once it is done I will update this answer with a download link.

UPDATE:
In the end, it is probably easiest to handle the T-SQL wrapper object creation manually. You can have Visual Studio generate the Assembly and any of the necessary DB settings statements but then do the CREATE PROCEDURE, CREATE FUNCTION, etc yourself by doing:

  1. In Project Properties -> Project Settings check the Create script (.sql file) check box.
  2. In Project Properties -> SQLCLR uncheck the Generate DDL check-box.
  3. Add New Item -> SQL Server -> User Scripts -> Post-Deployment Script and add your CREATE statements in this script.

Upvotes: 6

Related Questions