How catch the db exception in stored procedure

[Guvera]

Hi,

I am using Sql Server 2005. I have one doubt. I write procedure for multiple queries.

BEGIN TRAN
BEGIN TRY

query 1

query 2

query 3

COMMIT TRAN

END TRY
BEGIN CATCH
ROLLBACK TRAN

END CATCH

Now i am getting error in the query 1 or 2 or 3 . that is data base error For eg autoid parameter is not supplied . How can i catch the error? And what command is used for this one. I need this one very urgent.Hope your's reply.

Thanks

[NeedSomeAnswers]

You need to use @@Error.

When you run an sql statement in a stored proc immediately afterwards check if @@Error equals Zero. If Not then the statement has errored.

Here is a basic example.

BEGIN TRAN

SELECT * FROM TEST

IF @myERROR != 0 GOTO HANDLE_ERROR

COMMIT TRAN

HANDLE_ERROR:

ROLLBACK TRAN

[brucevde]

SQL Server 2005 introduced better error handling than @@Error.

In the Catch block execute a select statement that returns the error information.

    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

http://msdn.microsoft.com/en-us/libr...6(SQL.90).aspx