September 26, 2011 at 3:24 am
hi,
IN TRY...CATCH processing you can nest or have multiple TRY...CATCH blocks in your code.
The following although not very practical illustrates how the error is caught
and then processing continues
and the error is caught again and processing continues again.
but for me process should not continue once an error occured it should abort the process for very first
error it self .can any one help me to achieve this
for example:
------------------------------------------
BEGIN TRY
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
September 26, 2011 at 3:45 am
is this inside a stored procedure? If it is, you can use a return statement within the catch block to return to the caller without executing the remaining code.
CREATE PROCEDURE dbo.spTest (...)
AS
BEGIN
BEGIN TRY
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
-- Exit the procedure. Indicate to the
-- caller where it went wrong by returning
-- a custom error number.
RETURN 50000;
END CATCH;
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
-- Exit the procedure. Indicate to the
-- caller where it went wrong by returning
-- another custom error number.
RETURN 50001;
END CATCH;
-- Indicate success to the caller.
RETURN 0;
END
September 26, 2011 at 3:55 am
You could also capture the error in the inner CATCH block and throw it again with RAISERROR:
BEGIN TRY
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
--SELECT ERROR_NUMBER() AS ErrorNumber
-- , ERROR_SEVERITY() AS ErrorSeverity;
RAISERROR(ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE());
END CATCH;
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
-- Gianluca Sartori
September 26, 2011 at 4:06 am
Or, by changing the order a little bit, you can make the outer catch block catch first procedure's errors, while the 2nd procedure's errors are caught by the inner catch. Result will be that the 2nd procedure is not called if the 1st procedure fails and the 'SELECT @result = 0' is only reached if both procedure succeed.
DECLARE @result INT;
BEGIN TRY
EXECUTE @result = usp_ExampleProc1
BEGIN TRY
EXECUTE @result = usp_ExampleProc2
SELECT @result = 0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
-- Set the result to a custom error number.
select @result = 50000;
END CATCH;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
-- Set the result to another custom error number.
select @result = 50001;
END CATCH;
-- Show the result.
select @result;
edit: forgot to change the comments in the example code.
September 26, 2011 at 4:06 am
Further i am planing to log the error , so raise error method will not be much helpful i think so .........
September 26, 2011 at 4:17 am
For continuously calling usp_exampleproc until an error occurs you could of course use a while loop:
DECLARE @result INT = 0;
WHILE @result = 0
BEGIN
BEGIN TRY
EXECUTE @result = usp_ExampleProc
SELECT @result = 0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
-- Set a custom error number.
SELECT @result = 50000;
END CATCH;
END
-- Show the result.
SELECT @result;
To build in a graceful exit point, you can use the BREAK statement after some condition is met (i.e. put an IF in your loop, checking whether it needs to execute the BREAK). BREAK exits the WHILE loop at the point where BREAK is executed, even if the WHILE condition has not been met.
September 26, 2011 at 5:15 am
For continuously calling usp_exampleproc until an error occurs you could of course use a while loop:
Why would one like to do so?? I can't understand.:unsure:
For logging you can write your logging code in catch block of the main calling SP. And for all the inner calls in the catch block just use RAISERROR and Return statements.
September 26, 2011 at 5:46 am
nikethan1985 (9/26/2011)
Further i am planing to log the error , so raise error method will not be much helpful i think so .........
You're catching the error in the outer catch block, you can log there.
-- Gianluca Sartori
September 26, 2011 at 7:47 am
Sudhir Dwivedi (9/26/2011)
For continuously calling usp_exampleproc until an error occurs you could of course use a while loop:
Why would one like to do so?? I can't understand.:unsure:
OP has to answer that. He has originally posted an example calling the same procedure repeatedly (or twice at least). I have not been told what the internals of usp_exampleproc are...
Doing this isn't that un-logical as it may seem at first. For example if you're trying to process a queue. Processing messages from a service broker queue is often implemented using an infinite while loop. Only when the receive() statement times out -because the queue is empty for a prolonged period-, the loop is exited using a break statement and the handler procedure stops, to be started by service broker again when a next batch of messages comes available.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply