Post reply

Best Practices for Passing 50 or More Parameters to Stored Proc

  • January 23, 2009 at 9:13 am

    #127583

    This question is one that's been twirling around in my brain for a while.

    I've used various coding methodologies to insert data into a SQL Server table: command objects/parameters with stored procs (ADO and ADO.Net), using an ADO recordset to assign values to fields, passing an array or comma-delimited string to a stored proc, etc. I've also read about SQL Server 2008 and table valued parameters.

    It would be nice to pass something like a self-describing packet of data to the stored proc (XML) that could be parsed by the server.

    I'd like to hear from everyone on what they consider to be the best way to insert or update data into a table using ADO.Net when the number of updated values exceeds some large number of columns, let's say 50 to 80 columns of data. I know everyone has a preference, but I'd like to try to get some unbiased feedback.

    Thanks

  • January 26, 2009 at 7:09 am

    #932577

    In my experience, it depends on the number of rows. Opening XML is somewhat expensive inside SQL Server. I've found that if I'm moving fewer than ~100 rows (and yes, that's a rough number), then the XML is far too expensive and using comma delimited lists outperforms the XML.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 28, 2009 at 8:05 am

    #933841

    Yeah, I've been using comma-delimited lists myself. My question really is a matter of a clean method for passing a large number of parameters to a stored proc using ADO.Net. Number of rows is not the primary concern.

    I'm seeking input on a clean way to accomplish this without using some crazy looking coding method. In classic ADO, it was easy enough to use a disconnected recordset which when properly coded with page size, cursor location & locking method made it fairly easy to map values to columns and reconnect to peform the insert or update. Any ideas are welcome.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply