Adding Multiple Rows via (?)Stored Procedure

Giganews Newsgroups
Subject: Adding Multiple Rows via (?)Stored Procedure
Posted by:  Gene Wirchenko (gen…@ocis.net)
Date: Thu, 31 Mar 2011

Dear SQLers:

    So now I know how to get the batch's id.  I could code something
like:

          insert into Batches ('Example Batch')

          declare @BatchNr int
          select @BatchNr=scope_identity()

          insert into Transactions
          (TrnDt,BatchNr,Account,Amount)
          values
          ('20110331',@BatchNr,'Income',-500),
          ('20110331',@BatchNr,'Reserves',200),
          ('20110331',@BatchNr,'Income Tax S/A',200)

    This is, of course, not something that I want in app code.  I
would create a stored procedure.

    Is the correct approach to in app code create a cursor variable,
load it with the Transactions-to-be rows, and call the stored
procedure with the parameters BatchName and the cursor?  Is there a
better way?

    (You can post code if you want, but I really just want to know
the correct approach.  I would probably do better to try to do it
myself.)

Sincerely,

Gene Wirchenko

Replies