Professional Web Applications Themes

stored procedure that calls other procedures - Microsoft SQL / MS SQL Server

I have a more or less complex stored procedure that inserts/updates on a number of tables. It sets up a cursor through an inventory transaction's rows. For each row, it sets up another cursor for the item's inventory to calculate the inventory variation. Finally I want to call within the stored procedure, 3 other procedures to update other tables. I want the whole procedure, including the 3 other procedures called to work as a transaction, and if any of the inserts or updates fail for whatever reason, the database to remain intact and the transaction abort as a whole. Is ...

  1. #1

    Default stored procedure that calls other procedures

    I have a more or less complex stored procedure that inserts/updates on a
    number of tables.

    It sets up a cursor through an inventory transaction's rows.
    For each row, it sets up another cursor for the item's inventory to
    calculate the inventory variation.
    Finally I want to call within the stored procedure, 3 other procedures to
    update other tables.

    I want the whole procedure, including the 3 other procedures called to work
    as a transaction, and if any of the inserts or updates fail for whatever
    reason, the database to remain intact and the transaction abort as a whole.

    Is this possible? Does anyone has an example of a beast like this?

    Best regards,
    Edgard L. Riba


    Edgard Guest

  2. #2

    Default Re: stored procedure that calls other procedures

    You might want to post code.

    Ray Higdon MCSE, MCDBA, CCNA

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ray Guest

  3. #3

    Default Re: stored procedure that calls other procedures

    Stefan Gustafsson (se) writes: 

    Unfortunately, it is not so simple. SET XACT_ABORT ON does not cater
    for all errors. It does not handle compilation errors, and they can
    occur at run-time to, thanks to deferred name resolution. Neither does
    XACT_ABORT ON cover error 266, mismatch in trancount. And finally,
    RAISERROR does not cause the batch to abort.



    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: stored procedure that calls other procedures

    [posted and mailed, please reply in news]

    Edgard L. Riba (com) writes: 

    Many questions. I've just finished two articles on error handling in
    SQL Server. One is a background which tells how SQL Server behave. The
    other goes directly into how you should implement error handling.

    While the background article logically comes first, you may want to start
    with the implementation article. It is at:
    http://www.algonet.se/~sommar/error-handling-II.html

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  5. #5

    Default Re: stored procedure that calls other procedures

    Thanks Erland. Very interesting stuff. I'm printing the articles and
    will study them carefully.
    Best regards,
    Edgard L. Riba

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... [/ref]
    to 
    >
    > Many questions. I've just finished two articles on error handling in
    > SQL Server. One is a background which tells how SQL Server behave. The
    > other goes directly into how you should implement error handling.
    >
    > While the background article logically comes first, you may want to start
    > with the implementation article. It is at:
    > http://www.algonet.se/~sommar/error-handling-II.html
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Edgard Guest

Similar Threads

  1. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  2. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  3. Help, MVS DB2 Remote Stored Procedure calls
    By PWSmith in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:59 AM
  4. Trapping errors from stored procedure calls
    By Robert Carnegie in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 12:37 PM
  5. Creating a trigger that calls a stored procedure
    By Elie in forum Oracle Server
    Replies: 2
    Last Post: December 29th, 06:13 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139