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

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