Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Unanswered: stored proc w/error handling

    I am trying to write a simple sp that has simple eror handler for say a sp that deletes a user/updates a table ??Ive looked in my books and have found nothing
    I know the create proc etc
    but dont see anything that applies to it with error handling

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you could post a little more info on what your error handeling should do I or someone can probably help you.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2002
    Posts
    3

    Re: stored proc w/error handling

    You may do something like this

    create procedure thisisatest
    @param1 type1,
    @param2 type2,

  4. #4
    Join Date
    Sep 2002
    Posts
    3

    Re: stored proc w/error handling

    [QUOTE]Originally posted by mgomezb
    [B]You may do something like this

    Sorry I did something little before and I got cut-off , but continuing:

    create procedure thisisatest
    @param1 type1,
    @param2 type2,
    @IOStatus int = 0 output
    AS

    /* to follow your idea */
    begin transaction
    delete from table1 where col1 = @param1 and col2 = @param2

    set @IOStatus = @@Error

    if @IOStatus = 0
    commit transaction
    else
    rollback transaction

    -------------------
    Further more in the calling code you can have something like:
    -------------------

    declare @status int
    exec ThisIsATest @p1, @p2, @Status output

    if @Status = 0
    -- exec success code
    else
    -- exec failure code


    --------------------
    If you see, the stored procedure code rolled back the transaction (delete) while in the calling code you can execute additional code to take care of what happen if the stored procedure fails.

    Hope this help,

    MG.-

Posting Permissions

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