Variable numbers of parameters in a SqlClr UDF or Stored Proc

extended-stored-proceduresql-clr

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?

Best Answer

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.

Related Solutions

Sql-server – nyway to make sql server search the master database for SQLCLR stored procedures before the current one

I don't know a solution (and I don't know if the CLR scaffolding was ever designed to mimic the functionality you're talking about), but one workaround could be to create a T-SQL stored procedure in master that serves as a wrapper to relay the call to the CLR version. Marking it as a system object shouldn't be necessary, as long as a stored procedure with the same name doesn't exist in the user database.

EDIT - just proved that this works fine, as much for myself as anything. First I created your assembly and added the procedure necessary to repro (I'm not going to include all the bits here for brevity):

USE master;
GO
CREATE ASSEMBLY [Justin]
    AUTHORIZATION [dbo]
    FROM 0x4D5A900003000000040... lots of data here ...;
GO
ALTER ASSEMBLY [Justin]
    DROP FILE ALL
    ADD FILE FROM 0x4D6963726F... even more data here ...;
    AS N'Justin.pdb';
GO
CREATE PROCEDURE [dbo].[sp_RAISERROR_CaughtDemo]
AS EXTERNAL NAME [Justin].[StoredProcedures].[RaiserrorCaught]
GO

Then I created a wrapper stored procedure, making sure to fully qualify the CLR procedure:

CREATE PROCEDURE dbo.sp_RAISERROR_CaughtDemo_Wrapper
AS
BEGIN
    SET NOCOUNT ON;
    EXEC master.dbo.sp_RAISERROR_CaughtDemo;
END
GO

Then I adjusted your repro code to call the wrapper from tempdb instead:

USE tempdb;
PRINT 'USING tempDb';
EXEC sp_RAISERROR_CaughtDemo_wrapper;
GO

Results:

USING tempDb
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

So this shows that you should be able to use a wrapper like this to be able to call CLR procedures, unreferenced, from other databases.

(However, I will suggest that in general this shouldn't be a goal - you should be properly defining references with three-part names where applicable.)

I'll confess I did make one change to your stored procedure though, to prevent Visual Studio from whining at me. I changed:

while (true)

To:

while (i <= 11)

But of course this change had nothing to do with the scoping issue.

Sql-server – Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly

Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly?

SAFE CLR procedures do not crash SQL Server, so looking at the managed memory usage of CLR is unlikely to lead you to the root cause.

It's more likely that you have unsafe CLR code interacting with native code that has a memory leak. So look at any the CLR projects for any use of 3rd party native components (like OleDB or ODBC drivers), or any use of P/Invoke or COM Interop.