February 16, 2010 at 7:15 am
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
February 16, 2010 at 10:16 pm
Ashok,
Are you saying that you need to test for errors after each query in order to know, for example, that the error happened in query 2 rather than query 1 or 3? Or are you asking how to catch the specific error that is being thrown?
Tim Januario
February 16, 2010 at 10:25 pm
Hi,
Thanks for your reply. Yes, I need to catch the error information thrown.
What is command need to execute in the catch block?
Thanks
February 16, 2010 at 10:40 pm
Begin Catch
--U can Reaise Error or Sipmly return error message for test
SELECT Error_message()
end Catch
February 18, 2010 at 10:08 am
Another alternative to consider is nested try/catch to allow you to specifically narrow to each query. Something like the following may be a template for what you can try:
BEGIN TRY
BEGIN TRANSACTION;
BEGIN TRY
--query 1;
END TRY
BEGIN CATCH
--Modify this to select necessary info
SELECT ERROR_NUMBER() [ErrorNumber]
,ERROR_SEVERITY() [ErrorSeverity]
,ERROR_STATE() [ErrorState]
,ERROR_PROCEDURE() [ErrorProcedure]
,ERROR_LINE() [ErrorLine]
,ERROR_MESSAGE() [ErrorMessage];
--Or use this to raise error to outer catch and stop further execution
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE();
--this can be futher modified so look at BOL to see
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
BEGIN TRY
--query 2;
END TRY
BEGIN CATCH
--Modify this to select necessary info
SELECT ERROR_NUMBER() [ErrorNumber]
,ERROR_SEVERITY() [ErrorSeverity]
,ERROR_STATE() [ErrorState]
,ERROR_PROCEDURE() [ErrorProcedure]
,ERROR_LINE() [ErrorLine]
,ERROR_MESSAGE() [ErrorMessage];
--Or use this to raise error to outer catch and stop further execution
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE();
--this can be futher modified so look at BOL to see
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
BEGIN TRY
--query 3;
END TRY
BEGIN CATCH
--Modify this to select necessary info
SELECT ERROR_NUMBER() [ErrorNumber]
,ERROR_SEVERITY() [ErrorSeverity]
,ERROR_STATE() [ErrorState]
,ERROR_PROCEDURE() [ErrorProcedure]
,ERROR_LINE() [ErrorLine]
,ERROR_MESSAGE() [ErrorMessage];
--Or use this to raise error to outer catch and stop further execution
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE();
--this can be futher modified so look at BOL to see
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
--made it here, commit it
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION;
END;
END TRY
BEGIN CATCH
IF (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION;
END;
SELECT ERROR_NUMBER() [ErrorNumber]
,ERROR_SEVERITY() [ErrorSeverity]
,ERROR_STATE() [ErrorState]
,ERROR_PROCEDURE() [ErrorProcedure]
,ERROR_LINE() [ErrorLine]
,ERROR_MESSAGE() [ErrorMessage];
END CATCH;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply