executing multiple procs at one go

Hi,

I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -

EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5

Thankscreate proc dbo.A as
declare @.rc int, @.error int, @.cmd varchar(255)
set @.error = 0
EXEC @.rc = SP1
if @.rc != 0 set @.error = 1
EXEC @.rc = SP2
if @.rc != 0 set @.error = 2
EXEC @.rc = SP3
if @.rc != 0 set @.error = 3
EXEC @.rc = SP4
if @.rc != 0 set @.error = 4
EXEC @.rc = SP5
if @.rc != 0 set @.error = 5

if @.error = 1 begin
set @.cmd = 'Failed to execute SP' + cast(@.error as varchar(10))raiserror (@.cmd, 15, 1)
return (1)
end
return (0)|||Thanks. I shall test it soon and let you know if I have any problems.|||If more than one fails this logic will report the last one. In order to capture all failed you'll have to create a temp table (either # or @.) and insert a record with corresponding info after each SP fired. At the end of execution check for presence of rows in that table and if found, - construct the error message based on contents.|||Yeah, but if you have to rollback, your logging would rollback as well, and you'll have no idea...

echo out to a text file

Also some errors just raise and you can't trap them...I wonder what @.rc would be set to in those cases...NULL?

@.@.ERROR might be non zero in that case...

I'll have to test it...

Vivek: Let us know how it goes...