3

Lets say I have the following signature for a CLR Stored Proc or UDF:

[Public]
public static void RaisError(
    string message, 
    short severity, 
    short state, 
    params object[] args)

The generated DDL is

CREATE PROCEDURE [dbo].[RaisError]
    @message [nvarchar](4000), 
    @severity [smallint],
    @state [smallint],
    @args /* Error: Unsupported type. */
AS EXTERNAL NAME [SqlSaturdayClr].[StoredProcedures].[RaisError];

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does?

From this codeproject article it seems that extended stored procs intrinsically have a varariable number of variables and you have to enforce variable length in the code. Is my only option to use an extended stored procedure?

  • This seems like an ideal use case for table-valued parameters. – Aaron Bertrand Apr 13 '14 at 11:02
  • @AaronBertrand table value parameters would work, but thats a lot of boilerplate code. – Justin Dearing Apr 13 '14 at 13:27
  • @AaronBertrand Actually, I think I could make that pretty elegant. – Justin Dearing Apr 13 '14 at 13:34
2

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does?

No, there's no built-in way to do this.

Is my only option to use an extended stored procedure?

You should avoid writing an extended stored procedure, since these have been deprecated since SQL Server 2005 and generally cause more problems than they solve. There is no really good way to simulate the optional parameter behaviour of things like sp_executesql with a SQLCLR procedure or function.

Various workarounds can be suitable depending on the situation. The most often recommended is a T-SQL procedure or function wrapper, though I have also seen people experiment with passing a SQLCLR type (containing an array of values, for example) as well. The T-SQL equivalent to that would be a table-valued parameter.

  • I know extended stored procs are deprecated, and I'm sure I'll discover they're a pain to build and deploy. However, it seems that in the end they would produce the most succinct syntax. – Justin Dearing Apr 13 '14 at 13:35
  • 1
    @Justin what's the advantage of slightly more succinct syntax that you may have to re-write soon? – Aaron Bertrand Apr 13 '14 at 13:53
  • @JustinDearing "Pain" is far too small a world to describe the process. Even if you succeed, once you have an XP you've written yourself running inside the sqlserver.exe process space without managed code protections, you'll ascribe every future system weirdness to a bug in your XP (and you might well be right). I implore you not to go down this path. Find another solution, or change the design. – Paul White Apr 13 '14 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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