Professional Web Applications Themes

Cannot trap error returned by stored procedure in SQL Server 2000: Suggestions? - Microsoft SQL / MS SQL Server

I created two procs in Query yzer as follows. DROP PROCEDURE insjobs GO CREATE PROCEDURE insjobs AS INSERT INTO venkatpk values (1,'joe') IF error <> 0 RETURN 88 GO DROP PROCEDURE insjobsparent GO CREATE PROCEDURE insjobsparent AS DECLARE err INT BEGIN TRAN INSERT INTO jobs values ('TPR',10,25) -- call teh insjobs child procedure exec insjobs --if it returns an error rollback and dispaly err code select err = error IF err <> 0 BEGIN ROLLBACK TRAN select err END ELSE COMMIT TRAN GO The proc insjobs will cause an error because id 1 already exists as a primary key in ...

  1. #1

    Default Cannot trap error returned by stored procedure in SQL Server 2000: Suggestions?

    I created two procs in Query yzer as follows.

    DROP PROCEDURE insjobs
    GO
    CREATE PROCEDURE insjobs
    AS

    INSERT INTO venkatpk values (1,'joe')

    IF error <> 0
    RETURN 88

    GO
    DROP PROCEDURE insjobsparent
    GO
    CREATE PROCEDURE insjobsparent
    AS

    DECLARE err INT

    BEGIN TRAN

    INSERT INTO jobs values ('TPR',10,25)

    -- call teh insjobs child procedure
    exec insjobs

    --if it returns an error rollback and dispaly err code

    select err = error
    IF err <> 0
    BEGIN
    ROLLBACK TRAN
    select err
    END
    ELSE
    COMMIT TRAN

    GO

    The proc insjobs will cause an error because id 1 already exists as
    a primary key in table venkatpk. So I expect 88 to be returend to procedure
    insjobsparent. As a result teh INSERT into jobs should be rolled back.

    WHat I see happening is that
    a) The Unique key violation message is being displayed in the results pane of
    Query yzer when I thought it would be trapped by the error check in
    insjobs procedure.
    b) No rollback occurs, the insert to the jobs table should have been rolled
    back because of the failure in the called child procedure.

    It looks like I am not trapping the return code of 88 in the procedure
    insjobsparent even though I am retuning 88 from procedure insjob.
    The Select err value is not being displayed.

    Why am I seeing these results. Can I not trap return codes from stored
    procedures?


    Thanks

    DRD
    Dr Guest

  2. #2

    Default Re: Cannot trap error returned by stored procedure in SQL Server 2000: Suggestions?

    You need to assign the stored procedure return code to a variable. For
    example::

    DECLARE ReturnCode int
    EXEC ReturnCode = insjobs
    IF ReturnCode <> 0 ROLLBACK ELSE COMMIT

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Dr Deadpan" <com> wrote in message
    news:google.com... 
    procedure 
    pane of 
    check in 
    rolled 
    procedure 


    Dan Guest

Similar Threads

  1. SQL Server Stored Procedure Authentication
    By airnewhouse in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 9th, 09:19 PM
  2. RecordCount with Stored Procedure in SQL Server
    By Ken VdB in forum ASP Database
    Replies: 8
    Last Post: October 21st, 03:01 PM
  3. Replies: 7
    Last Post: September 11th, 07:03 PM
  4. C stored procedure SQL error.
    By Craig in forum IBM DB2
    Replies: 2
    Last Post: July 11th, 04:44 PM
  5. Can "Computed Column" be a stored procedure in SQL Server 2000?
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 06:47 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