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

Question

I'm trying to work out what the best method to passing a large number of parameters into a stored procedure.

Some methods i was thinking about are;

1. Create the relevant database objects, including a parameter object for each item and invoke the command object

2. Pass in an XML document and have the stored procedure unpack it. (The app will already them in XML format)

If anyone has any better ideas, i'm open to hear them

Thanks.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

Okay, this will likely be downvoted, but... Just don't pass so many parameters. Instead of trying to find a way to do something that is inherently hard in the language, maybe realize that that's the language's way of telling you that you need to redesign what you're doing. Just like many insects use color to indicate "do not eat", when you find yourself doing something basic that requires many hoops to execute, maybe you need to rethink what you're doing.

In particular, I think possibly you should try to figure out why the stored procedure needs so many parameters; can you decompose this into a set of smaller, simpler stored procedures? Maybe if you have them communicating through temp tables, you can get the same effect, but with less... ick.

Answer 5

The February 2009 issue of SQL Server Magazine has an excellent article for using arrays (More Options for Handling Arrays as Inputs). The article describes using a CLR split UDF. This works with SQL 2005 since it has CLR integration. Its too much code for me to post here and explain, but if you have access to the magazine's website I recommend downloading their function for doing this. Its fast and elegant.