multiple sets of data to one sp for batch updating

multiple sets of data to one sp for batch updating

Post by Rayne Bai » Thu, 06 Nov 2003 17:53:57



Is there any way to pass multiple values to a stored procedure as a set of values. For example I'd have an application that must do multiple saves of data...this set of data first, then a second and third set of data. But I currently have the VB code to run the first update, then the second then third, but if the 2nd or 3rd fails, because it is in VB, the first save is still there.

How do I pass the 2nd and 3rd set of data to the stored procedure so that it will run all the updates in one large batch. That way if one part fails, the whole thing will rollback? I'm new to all this SQL/VB stuff.

the vb currently uses RDO and the application is fairly large (I'm managing code from a previous programmer)

*********************************************************
Rayne Bair

 
 
 

multiple sets of data to one sp for batch updating

Post by Tom Morea » Thu, 06 Nov 2003 17:58:26


Consider XML:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsq...
html/sql01c5.asp

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


Is there any way to pass multiple values to a stored procedure as a set of
values. For example I'd have an application that must do multiple saves of
data...this set of data first, then a second and third set of data. But I
currently have the VB code to run the first update, then the second then
third, but if the 2nd or 3rd fails, because it is in VB, the first save is
still there.

How do I pass the 2nd and 3rd set of data to the stored procedure so that it
will run all the updates in one large batch. That way if one part fails, the
whole thing will rollback? I'm new to all this SQL/VB stuff.

the vb currently uses RDO and the application is fairly large (I'm managing
code from a previous programmer)

*********************************************************
Rayne Bair

 
 
 

multiple sets of data to one sp for batch updating

Post by Rayne Bai » Thu, 06 Nov 2003 18:43:03


Is there a way to group stored procedures (each stored procedure runs it's commands in batch). So could I do something like this:

strSQL = "EXEC SAVE_CLAIM(" & PARAM1 & ", '" & PARAM2 & '");"
strSQL = strSQL & "EXEC SAVE_OFFENDERS("...PARAMETERS...);"
strSQL = strSQL & "EXEC SAVE_COMPANIONS(...)"

set rs = connection.execute(strSQL)

would that work if each of those stored procedures is rolled into a transaction (inside the procedure the commands are executed in a transaction)? Would it execute in batch so that if the 2nd stored procedure failed the first would also rollback? I read somewhere about using CALL, do I have to add that in somewhere? I currently do my stored procedures as {CALL StoredProc(...)}, but not sure how to do that with multiple stored procs.

*********************************************************
Rayne Bair


  Consider XML:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsq...

  --
  Tom

  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON Canada
  www.pinnaclepublishing.com/sql


  Is there any way to pass multiple values to a stored procedure as a set of values. For example I'd have an application that must do multiple saves of data...this set of data first, then a second and third set of data. But I currently have the VB code to run the first update, then the second then third, but if the 2nd or 3rd fails, because it is in VB, the first save is still there.

  How do I pass the 2nd and 3rd set of data to the stored procedure so that it will run all the updates in one large batch. That way if one part fails, the whole thing will rollback? I'm new to all this SQL/VB stuff.

  the vb currently uses RDO and the application is fairly large (I'm managing code from a previous programmer)

  *********************************************************
  Rayne Bair

 
 
 

multiple sets of data to one sp for batch updating

Post by Tom Morea » Thu, 06 Nov 2003 19:55:42


I'm cross-posting this to the ADO group, since you'd build the query in ADO
before sending to SQL Server.  You can certainly begin a transaction and run
your procs within the scope of the transaction.  If you rollback anywhere,
it will rollback the whole transaction.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


Is there a way to group stored procedures (each stored procedure runs it's
commands in batch). So could I do something like this:

strSQL = "EXEC SAVE_CLAIM(" & PARAM1 & ", '" & PARAM2 & '");"
strSQL = strSQL & "EXEC SAVE_OFFENDERS("...PARAMETERS...);"
strSQL = strSQL & "EXEC SAVE_COMPANIONS(...)"

set rs = connection.execute(strSQL)

would that work if each of those stored procedures is rolled into a
transaction (inside the procedure the commands are executed in a
transaction)? Would it execute in batch so that if the 2nd stored procedure
failed the first would also rollback? I read somewhere about using CALL, do
I have to add that in somewhere? I currently do my stored procedures as
{CALL StoredProc(...)}, but not sure how to do that with multiple stored
procs.

*********************************************************
Rayne Bair



  Consider XML:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsq...
html/sql01c5.asp

  --
  Tom

  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON Canada
  www.pinnaclepublishing.com/sql



  Is there any way to pass multiple values to a stored procedure as a set of
values. For example I'd have an application that must do multiple saves of
data...this set of data first, then a second and third set of data. But I
currently have the VB code to run the first update, then the second then
third, but if the 2nd or 3rd fails, because it is in VB, the first save is
still there.

  How do I pass the 2nd and 3rd set of data to the stored procedure so that
it will run all the updates in one large batch. That way if one part fails,
the whole thing will rollback? I'm new to all this SQL/VB stuff.

  the vb currently uses RDO and the application is fairly large (I'm
managing code from a previous programmer)

  *********************************************************
  Rayne Bair