XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   Gene Wirchenko (genew@ocis.net) writes:   
   >      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?   
      
   With SQL 2008, and if your client API supports it, the correct approach   
   is to pass the transactions in a table-valued parameter. Else you can   
   use XML to send all data at once.   
      
   See http://www.sommarskog.se/arrays-in-sql-2008.html for an introduction   
   to TVPs and http://www.sommarskog.se/arrays-in-sql-2005.html#XML for how   
   to use XML.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)