July 2, 2009 at 7:42 am
Hi,
I am a bit confused about the way RAISERROR works. I have a wrapper SP which calls 2 SP's within it in sequence. In the 1st SP I have error raised. I expected that the 2nd SP would not get executed. However it does. It doesn't seem very intutive. Can someone explain to me what exactly is happening?
CREATE TABLE [dbo].[DEPARTMENT](
[DEPT_ID] [int] NOT NULL,
[DEPT_NAME] [varchar](30)
CONSTRAINT [PK_DEPARTMENT] PRIMARY KEY CLUSTERED
(
[DEPT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE procedure [dbo].[SPMy_wrapper_raisererror] as
begin
RAISERROR (N'Gods must be crazy', -- Message text.
16, -- Severity.
1 -- State.
)with nowait;
end
CREATE procedure [dbo].[SPMy_wrapper_insert] as
begin
INSERT INTO [TEST].[dbo].[DEPARTMENT]
([DEPT_ID]
,[DEPT_NAME])
VALUES
(200 , 'GOD')
end
CREATE procedure [dbo].[SPMy_wrapper] as
begin
select count(*) from department
exec SPMy_wrapper_raisererror
exec SPMy_wrapper_insert
select count(*) from department
end
When i run the SP SPMy_wrapper the SP SPMy_wrapper_insert still gets executed inspite of the error raised in the previous SP.
What am I missing here 🙁
Regards,
Anand
July 2, 2009 at 7:50 am
i think if you want code to stop on an error, you want to use SET XACT_ABORT ON and use a transaction...in that situation, when an error is raised, execution stops and the transaction is rolled back:
CREATE procedure [dbo].[SPMy_wrapper] as
begin
SET XACT_ABORT ON
BEGIN TRAN
select count(*) from department
exec SPMy_wrapper_raisererror
exec SPMy_wrapper_insert
select count(*) from department
COMMIT TRAN
end
otherwise, each statement is evaluated and commited seperately from the line previous...
Lowell
July 2, 2009 at 7:55 am
Following the execution of the raiserror sproc you should look at @@ERROR
It would look like this:
IF ( @@ERROR = 0 )
BEGIN
EXEC SPMy_wrapper_insert
END
CEWII
July 2, 2009 at 8:00 am
Elliott (7/2/2009)
Following the execution of the raiserror sproc you should look at @@ERRORIt would look like this:
IF ( @@ERROR = 0 )
BEGIN
EXEC SPMy_wrapper_insert
END
CEWII
that is the advantage of SET XACT_ABORT ON: it automatically evaluates @@error, and either stops immediately, or allows the next statemtn to execute.
only if you have conditional errors, where if proc1 raised an error, you execute proc3 instead of proc2 would evaluating @@error be of any real use, right? at least compared to how XACT_ABORT works....
Lowell
July 3, 2009 at 1:16 am
Hmm... but if my levels of nesting is multiple and if i need to abort if error occurs at any level then i guess i would have to settle for checking @@ERROR. For example the code below does not break out in case of the error. The code SPMY_Wrapper is the original code which i provided earlier.
create procedure [dbo].[SPMy_Super_wrapper] as
begin
SET XACT_ABORT ON
BEGIN TRAN
select * from department
exec [SPMy_wrapper]
select * from department
COMMIT TRAN
end
I would like to do the transaction handling at the outermost layer only and not in each of the child SP's.
July 4, 2009 at 11:49 am
If you set transact abort at the 1st sproc it will be the option for all sub sprocs unless they change it themselves..
CEWII
July 6, 2009 at 11:01 pm
SET XACT_ABORT ON is one approach - though it has behaviour quirks of its own.
TRY...CATCH is your friend here.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 5:39 pm
I was actually counting on those quirks...
CEWII
July 7, 2009 at 5:55 pm
Elliott W (7/7/2009)
I was actually counting on those quirks...
Heh 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply