Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003

    Unanswered: executing multiple procs at one go


    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


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    create 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)
    return (0)

  3. #3
    Join Date
    Sep 2003
    Thanks. I shall test it soon and let you know if I have any problems.

  4. #4
    Join Date
    Jul 2003
    San Antonio, TX
    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.

  5. #5
    Join Date
    Nov 2002
    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...

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts