Error Handling

Varun (varun.stellar@yahoo.co.in) writes:
> I'm new to SQL server programming. Want to know more about error handling.
>
> To handle error i'm using following try/catch
>
> begin try
> ....
> end try
> begin catch
> Select Error_Message() as errormessage,
> Error_Number() as errornumber
> end catch
>
> To inform client which call procedure i consider following
> If procedure execute successfully then it returns 0 otherwise returns the
> errornumber. Is it OK?
>
> What about Trigger which are written for some table. How to inform user if
> some error occurred?
>
> Print statement is not useful because it print error message in console
> window. While accessing sql database from any client there is no console
> window.
> So what's the best way to display eror/inform user about them.

Using SELECT to return error messages is not that good idea, at least
not if you procedure can return regular result sets. The client will
be very confused, and not what is what.

As Phillipe suggested, use RAISERROR. This should raise an error client-
side. Exactly how, depends on the programming environment you have.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Error Handling 

Suppose there is a table for which i write some trigger which call during
inserting any record in table. I use RAISERROR in trigger. Now from client
program i insert any record in table. If RAISEERROR is called then will it
display any error message in client program?
I checked it. I called RAISEERROR in trigger but could not found any message
in client program (also not in server).
"Erland Sommarskog" wrote in message

> Varun (varun.stellar@yahoo.co.in) writes:
>> I'm new to SQL server programming. Want to know more about error
>> handling.
>>
>> To handle error i'm using following try/catch
>>
>> begin try
>> ....
>> end try
>> begin catch
>> Select Error_Message() as errormessage,
>> Error_Number() as errornumber
>> end catch
>>
>> To inform client which call procedure i consider following
>> If procedure execute successfully then it returns 0 otherwise returns the
>> errornumber. Is it OK?
>>
>> What about Trigger which are written for some table. How to inform user
>> if
>> some error occurred?
>>
>> Print statement is not useful because it print error message in console
>> window. While accessing sql database from any client there is no console
>> window.
>> So what's the best way to display eror/inform user about them.
>
> Using SELECT to return error messages is not that good idea, at least
> not if you procedure can return regular result sets. The client will
> be very confused, and not what is what.
>
> As Phillipe suggested, use RAISERROR. This should raise an error client-
> side. Exactly how, depends on the programming environment you have.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 >> Stay informed about: Error Handling 

Varun (varun.stellar@yahoo.co.in) writes:
> Suppose there is a table for which i write some trigger which call
> during inserting any record in table. I use RAISERROR in trigger. Now
> from client program i insert any record in table. If RAISEERROR is
> called then will it display any error message in client program? I
> checked it. I called RAISEERROR in trigger but could not found any
> message in client program (also not in server).

You need to reraise the error in the CATCH section:

SELECT @errmsg = error_message
RAISERROR(@errmsg, 16, 1)


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Error Handling 

Thanks. So if any trigger/procedure call RAISEERROR in CATCH section then
catch statement in client program can get this error. Right?

RAISERROR(@errmsg, 16, 1)Here you gave hard coded values.
Will 16 and 1 work everywhere? Or we should do it like
SELECT
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@errmsg, @ErrorSeverity , @ErrorState )


What about function. How to catch error for them. I think
BEGIN TRY....END TRY
BEGIN CATCH .. END CATCH dont work for them.
"Erland Sommarskog" wrote in message

> Varun (varun.stellar@yahoo.co.in) writes:
>> Suppose there is a table for which i write some trigger which call
>> during inserting any record in table. I use RAISERROR in trigger. Now
>> from client program i insert any record in table. If RAISEERROR is
>> called then will it display any error message in client program? I
>> checked it. I called RAISEERROR in trigger but could not found any
>> message in client program (also not in server).
>
> You need to reraise the error in the CATCH section:
>
> SELECT @errmsg = error_message
> RAISERROR(@errmsg, 16, 1)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Error Handling 

I tried to manually call RAISEERROR in some stored procedure (in this case
there was no exception)
I put some string in message variable and pass this in RAISEERROR. When when
i call that procedure in client program, it shows different message. It's
not the one which i passed in RAISEERROR.
Is it possible to get same message?
"Erland Sommarskog" wrote in message

> Varun (varun.stellar@yahoo.co.in) writes:
>> Suppose there is a table for which i write some trigger which call
>> during inserting any record in table. I use RAISERROR in trigger. Now
>> from client program i insert any record in table. If RAISEERROR is
>> called then will it display any error message in client program? I
>> checked it. I called RAISEERROR in trigger but could not found any
>> message in client program (also not in server).
>
> You need to reraise the error in the CATCH section:
>
> SELECT @errmsg = error_message
> RAISERROR(@errmsg, 16, 1)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Error Handling 

Varun (varun.stellar@yahoo.co.in) writes:
> Thanks. So if any trigger/procedure call RAISEERROR in CATCH section then
> catch statement in client program can get this error. Right?

Yes, but you should reraise consistently in your CATCH sessions. Say
that you have a stored procedure that fires a trigger, and that trigger
runs into an error. You have TRY-CATCH in the trigger, and in the CATCH
section you reraise. In your procedure you also have TRY-CATCH. You must
again reraise the error, for the error to reach the client.

The flip side of is that occassionally you may not want to bother the
client with the error message, for one reason or another. In that
case, you can opt to not reraise.

> RAISERROR(@errmsg, 16, 1)Here you gave hard coded values.
> Will 16 and 1 work everywhere? Or we should do it like
> SELECT
> @ErrorSeverity = ERROR_SEVERITY(),
> @ErrorState = ERROR_STATE();
> RAISERROR(@errmsg, @ErrorSeverity , @ErrorState )

That's actaully in better. I was in a hurry when I was at work this
morning.

> What about function. How to catch error for them. I think
> BEGIN TRY....END TRY
> BEGIN CATCH .. END CATCH dont work for them.

Right. You will have to let it suffice with catching the error in
the procedures that call them. But at least that is possible. In
SQL 2000, if there was an error when running a multi-statement
function, there was absolutely no way to detect this!

--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Error Handling 

> Yes, but you should reraise consistently in your CATCH sessions. Say
> that you have a stored procedure that fires a trigger, and that trigger
> runs into an error. You have TRY-CATCH in the trigger, and in the CATCH
> section you reraise. In your procedure you also have TRY-CATCH. You must
> again reraise the error, for the error to reach the client.

If i write some trigger for any Table (like trigger is written for before
insering data into table) then should i reraise for that table in CATCH
block?
It doesn't matter how many triggers are written for a single table (like
before insert, after insert, update etc) table will contain only one reraise
in its catch statement which will handle all cases right?
 >> Stay informed about: Error Handling 

Varun (varun.stellar@yahoo.co.in) writes:
> If i write some trigger for any Table (like trigger is written for before
> insering data into table) then should i reraise for that table in CATCH
> block?
> It doesn't matter how many triggers are written for a single table (like
> before insert, after insert, update etc) table will contain only one
> reraise in its catch statement which will handle all cases right?

Without any TRY-CATCH at all, an error in a trigger aborts the execution
of the entire batch, as does a ROLLBACK. (A RAISERROR along does not
terminate the batch.) Thus, if a table has multiple triggers, and one
has an error, the subsequent triggers will not execute at all.

From this follows that TRY-CATCH in triggers is not that equally important,
because you cannot run into the situation that you continue executing if
there is an error.

Nevertheless, it's always good practice to have a TRY-CATCH where its
possible. And if you have it, you should always reraise one way or another,
save for the situation you really want to suppress the error. Another
good thing to always have in the trigger is this:

IF @@trancount > 0 ROLLBACK TRANSACTION


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Error Handling