Use optional arguments in SQLCLR

Question

I have a stored procedure in MSSQL 2005 Server SQLCLR. Is it possible to define an optional argument to it? If so, how?

Answer 1

The quickest, if not tidiest, solution that comes to mind is to use a 'shim' stored procedure with default values for optional parameters that calls the inner stored procedure with these default values, much as you use method overloading for 'optional' parameters in C# 3 and earlier.

Answer 2

You can pass it as null, and then do something like this:

create proc sp_find_person
 @fname varchar(30)
 @lname varchar(60)
as
begin
 select * from person a
 where (a.fname = @fname or @fname is null) 
   and (a.lname = @lname or @lname is null);
end
go

Passing both parameters as null would return all rows from person.

Passing @fname with a value and @lname null would return all persons with that @fname.

Passing @lname with a value and @fname null would return all persons with that @lname.

Passing both with a non-null value would find all persons with that the specified fname and lname.