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

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
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 (AT) sommarskog (DOT) 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

You can load a table parameter. That locks you into proprietary code
and makes it harder to check that debits and credits balance (your
example did not, if i am reading it correctly).

You can use a long parameter list and do the math and validations in
the same procedure. This will be portable code.

--CELKO-- (jcelko212 (AT) earthlink (DOT) net) writes:
Most of all it will be completely unusable garbage, and SQL Server the
performance penalty is severe. It's an awfully bad suggestion.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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