How to create sproc with unlimited number of parameters?

We have this kind of thing in C#, and I know the sp_executesql (or something like that) does it too. How do you create a stored procedure that has certain known parameters, followed by any number of additional parameters?

For example, you may have one that's called in any of these ways:

exec my_sp @.ID, @.text, @.P1, @.P2

exec my_sp @.ID, @.text, @.P1, @.P2, @.P3

exec my_sp @.ID, @.text, @.P4, @.P39087, @.P2

I can't find a syntax in the docs that really accommodates this.

Thanks in advance for your help!

Is something like this what you are looking for? This defines a procedure with M required parameters and N optional parameters:

create procedure dbo.xample
( @.pm_required_01 <type#01>,
@.pm_required_02 <type#02>,
...
@.pm_required_M <type#M>,
@.pm_optional_01 <type#01> = <defaultValue#01>,
@.pm_optional_02 <type#02> = <defaultValue#02>,
...
@.pm_optional_N <Type#N> = <defaultValue#N>
)
as

yourCode

|||Does this solution enforce a maximum number of parameters though? If so, that's not quite what I was after.

I guess the other problem too is that there has to be some way to iterate through all of the parameters as well.|||

Jeff:

It sounds to me like you want to use a "list" as an argument to the stored procedure; if that is the case examine this article by Erland Sommarskog and see if this is the correct orientation:

http://www.sommarskog.se/arrays-in-sql.html

Also, there is a response here from Jens Suessmeyer that has some more about using a "string list":

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

TSQL has a hard limit of ~2K Parameters to any module. Thus, there is no way to do what your asking natively in TSQL (i.e, the best you can do is 2K sql_variant parameters with a default defined ).

People have used various techniques to get around this problem, most of which are nicely summarized in the links above.

How to create a stored procedure of this code (inline sql in aspx) ?

I have some SQL code as inline SQL (bad habit, I know). Now I want to convert this to an sproc, but I'm pretty much out of ideas here. The code looks like this:

string

SQL ="SELECT * FROM MyDBTable WHERE 1=1";if (txtMyField1.Text !="")

{

SQL = SQL +

" AND MyField1 = @.MyField";

}

if (txtMyField2.Text !="")

{

SQL = SQL +

" AND MyField2 LIKE '%'+ @.MyField2 + '%'";

}

if (txtMyField3.Text !="")

{

SQL = SQL +

" AND MyField3 LIKE '%' + @.MyField3 + '%'";

}

I have an search page built on ASP.NET 2.0. Based on what the user has entered to the form fields, the SQL in constructed on the fly. Since this is now inside codebehind file (aspx.cs), I want to get rid of it and move it to an sproc. But the question is how ? Some simple SQL clauses are easy to convert to an sproc but this is causing me lots of issues.

You may try this see if it is working or not:

CREATE PROCEDURE dbo.sp_Test (
@.MyField varchar(5) = null,
@.MyField2 varchar(5)= null,
@.MyField3 varchar(5)= null
)
AS

Begin
declare @.Syntax varchar(500)

SELECT @.Syntax = 'SELECT * FROM MyDBTable' +
'WHERE 1=1' +
CASE
WHEN @.MyField IS NOT NULL and @.MyField <> ''
THEN ' AND MyField1 = ''' + @.MyField + ''''
ELSE ''
END +
CASE
WHEN @.MyField2 IS NOT NULL and @.MyField2 <> ''
THEN ' AND MyField2 LIKE ''%'+ @.MyField2 + '%'''
ELSE ''
END +
CASE
WHEN @.MyField3 IS NOT NULL and @.MyField3 <> ''
THEN ' AND MyField3 LIKE ''%'+ @.MyField3 + '%'''
ELSE ''
END

EXECUTE(@.syntax)
End

You need adjust the size of the variables as request.

|||Thanks for the code. I'll try this later and let you know how it worked.|||Thanks JimmyM for your assistance, it worked !!! However you had an typo in your reply, since FROM was there twice. But this wasn't an issue.|||Sorry for the typo, glad that the solution is working for you.sql