error handling from stored procedure

Results 1 to 3 of 3

Thread: error handling from stored procedure

  1. #1
    Join Date
    Dec 1969

    Default error handling from stored procedure

    When calling a stored procedure in SQL Server, how can I access any errors that may occur so I can write a message to the user saying the task failed? I do checks for @@ERROR <> 0 in the stored procedure and rollback the transaction accordingly. I shouldn't have to return a value manually if @ERROR > 0, right?

  2. #2
    Join Date
    Dec 1969

    Default Why not?

    You *must* return the @@error variable out of the stored procedure. You can&#039;t magically know that an error occurred.<BR><BR>Just do:<BR>SELECT @l_error = @@ERROR<BR>IF @l_error &#060;&#062; 0<BR> -- rollback<BR> SELECT @l_err As ErrorNumber, CAST(1 AS BIT) AS ErrorOccurred<BR> RETURN<BR>END IF<BR><BR>-Doug

  3. #3
    Join Date
    Dec 1969

    Default RE: Why not?

    If you don&#039;t return any records, just an update or insert, you&#039;d better return the @@ERROR, using<BR><BR>SET @l_error = @@error<BR>RETURN(@ERROR)<BR><BR>You shouldn&#039;t use a SELECT to return 1 value IMHO.<BR><BR>When you do this you don&#039;t need to create a recordset, you just check the parameter RETURN_VALUE of the command object. Also something that causes an @ERROR, is also passed to the connection (or command) errors collection.

Posting Permissions

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