none
Forwarding Variable number of parameters from VB.2005 to Sql server 2005 Stored Procedure RRS feed

  • Question

  •  

    I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
    Tuesday, January 15, 2008 7:28 AM

All replies

  • If I understand you correctly you have a SQL proc that does inserts/updates (if it inserts or updates doesn'r really matter here)? I asume that proc takes as parameters the various values for one row in the table you want to inset/update? Now you want to be able to send an arbitrary number of rows to that proc.

    As SQL Server 2005 (and earlier) doesn't support table valued parameters (which SQL 2008 does) you have run into the age-old problem of multi-row updating in one network roundtrip.

    If this is the problem you have, then one way of solving it is to send the data as an xml blob into the proc, let the proc shred the blob and do the inserts/updates.

    Niels


    Tuesday, January 15, 2008 10:34 AM
    Moderator