send batch of sql statements to sql server

[JT]

can someone point me in the right direction for learning more about how to
go about sending a batch of sql statements from asp to sql server, and
having errors appropriately handled?

for example,

i want to send the following stored procedure calls to sql server in a
single batch from my asp code, rather than using a loop in asp to execute
each procedure individiually. if one of these fails, they all should fail.

exec myProc @param1 = 'A'
exec myProc @param1 = 'B'
exec myProc @param1 = 'C'
exec myProc @param1 = 'D'
exec myProc @param1 = 'E'
exec myProc @param1 = 'F'

thanks,

JT

 

[Bob Barrows [MVP]]

can someone point me in the right direction for learning more about
how to go about sending a batch of sql statements from asp to sql
server, and having errors appropriately handled?

for example,

i want to send the following stored procedure calls to sql server in a
single batch from my asp code, rather than using a loop in asp to
execute each procedure individiually. if one of these fails, they
all should fail.

exec myProc @param1 = 'A'
exec myProc @param1 = 'B'
exec myProc @param1 = 'C'
exec myProc @param1 = 'D'
exec myProc @param1 = 'E'
exec myProc @param1 = 'F'

thanks,

JT

Personally, I would encapsulate these in a stored procedure, using a
transaction:

CREATE PROCEDURE RunProcs (
@param1 char(1),
....,
@param6 char(1)) AS

declare @err int
BEGIN TRANSACTION
exec @err=myproc @param1
if @err <> 0
begin
rollback transaction
return 1
end
exec @err=myproc @param2
if @err <> 0
begin
rollback transaction
return 2
end
....
exec @err=myproc @param6
if @err <> 0
begin
rollback transaction
return 6
end
commit transaction

Bob Barrows