What is the best method in passing a large number of parameters in SQL Server

XML processing is very handy for handling large numbers of parameters. You can easily pass in your parameters in XML and then xml handling to get your values out. Its even better if your data is already .Net.

e.g.

DECLARE @WidgetsIds xml
SET @WidgetsIds ='<Widgets><id>3</id><id>6</id><id>15</id></Widgets >' 

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @Widgets.nodes('/widgets/id') as ParamValues(ID) 

This should return 3 rows : 3, 6, 15. Its easy to open this up to pull the values you need out and manipulate them.

If you can upgrade to SQL Server 2008, there's a new feature called Table-Valued Parameter exactly for this very purpose. See the Books Online section about it.

It basically allows you to create a table-valued user-defined type, which can then be used as a readonly input parameter into any stored procedure.

From .NET, you can simple use a DataTable instance and pass it arbitrary numbers of rows inside that data table.

Marc

How many parameters do you consider to be "a large number"? Also, why does the stored procedure have so many parameters? Sounds like it will be fun to test.

How will the code that calls the stored procedure come up with the data it needs to pass in the parameters? If the data are already available in XML, then XML might be a good way to pass them. If the data are already available in separate variables, then Parameters objects might be better.

Note also that with SQL Server 2008 you have the option of sending a DataTable as the value of a parameter of a TABLE type.

---

EDIT: Easy Way to Set Up Parameters

Although I believe that DataSets are over-used and abused, the following is a procedure that will set up stored procedure parameter objects and make it much easier to call stored procedures with many parameters:

1. Create a new DataSet by using "Add New Item" and choosing DataSet. Name the DataSet whatever you like.
2. View Server Explorer if you weren't already viewing it
3. Add a connection to your database if it's not already there
4. Expand the connection until you find your stored procedure
5. Drag the Stored Procedure onto the design surface

This will create a TableAdapter with a method on it to call your stored procedure. The method depends on the SqlParameter objects that the infrastructure will already have created. The method will take the SP parameters passed in as parameters of the method call, and will use the call parameters to fill in the SP parameters. This is all done for you, and is done in a reasonably efficient manner.