Catching error from UDF inside a SP

Question

Post reply

Catching error from UDF inside a SP

Mithrandir

SSCrazy

Points: 2628

April 21, 2004 at 4:48 pm

#107639

Hi all.

I have a UDF wich basically do some like this:

CREATE FUNCTION(
	@func_param
)RETURNS INT AS
BEIGN
DECLARE @result
IF @func_param > 10
	SET @result = 0/0 --raising an error, RAISERRROR() seems not to be available here
ELSE
	SET @result = @func_param
RETURN @result
END

I'm trying to create a stored procedure to do some updates

CREATE PROCEDURE dbo.MyProc(
	@param1 int,
	@param2 int,
	@out_param int output
)AS

BEGIN TRAN

UPDATE dbo.SomeTable
SET field2 = dbo.fnMyUDF(@param2)
WHERE keyfield = field1

IF @@Error <> 0
BEGIN
	SET @out_param = @@Error
	ROLLBACK TRAN
END
ELSE
BEGIN
	SET @out_param = @@Error
	COMMIT TRAN
END
GO

Here, if I call the sp:

EXEC dbo.MyProc 1,100, @sp_result output

The value for @sp_result is 0 and no update is done, seems not to catch the error generated by UDF, because always get @@error= 0.

Can you help me a little?

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Basically, @@Error only lasts for one successful line of code so by the time you get to the "SET @out_param = @@Error" chunk of code, @@Error has been reset to "0" because the "IF" was successful. You need to declare an INT variable (I use @MyError) well before you do any error checking and then make a little modification to your code as follows:

UPDATE dbo.SomeTable SET field2 = dbo.fnMyUDF(@param2) WHERE keyfield = field1 SET @MyError = @@Error IF @MyError BEGIN SET @out_param = @MyError ROLLBACK TRAN END ELSE BEGIN SET @out_param = @MyError COMMIT TRAN END GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mithrandir SSCrazy Points: 2628 More actions · Answer 2

Mithrandir

SSCrazy

Points: 2628

April 22, 2004 at 8:10 am

#503841

I did it, still the same.

But still getting 0. Annalyzing a bit more, the SP always take the commit way, never try to rollback.

From this I assume @@Error returns 0 even when the UDF is generating an error. The @@Error catch is immediately after the UPDATE query.

I don't know how to catch the UDF error.

help :-S

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 3

SteveB-74825

Hall of Fame

Points: 3290

April 22, 2004 at 8:30 am

#503847

ignore the post above - doh!

cheers

dbgeezer

Mithrandir SSCrazy Points: 2628 More actions · Answer 4

Mithrandir

SSCrazy

Points: 2628

April 22, 2004 at 8:40 am

#503850

Why?

It worked. =) (at least I can how to call rollback or commit)

Isn't the solution I'd wish, but certainly works.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 5

i'm digging myself a hole here !

i added a post advising you to try @@rowcount inside the function, not realising you did the insert in the SP - went in to edit the post and added 'ignore the above' - my original post was replaced.

just to make sure - i wasn't advising you to ignore the advice that turned out to be the correct answer.

... i'll get my coat .... 😉

cheers

dbgeezer