Friday, March 30, 2012

RaisError in a function? Any ideas

Writing a function and need to raise an error under certain conditions. This is a gross over-simplification of the function. But you get the idea....I need to use @.@.ROWCOUNT

Code Snippet

create Function GetTheValue

(@.Input INT)

returns Bigint

as

begin

declare @.Output bigint

select TheValue

from TheTable

where RowID = @.Input

IF @.@.ROWCOUNT = 0

RAISERROR('Oops',15,1)

return @.INput

END

Basically, the way this usually works is that you return an invalid value and check for it. Like in this case, you can never get a negative value for @.@.rowcount, just return -1 or something like this.

You can force an error to occur by doing something like 0/1, but I was never fond of that method since the error message doesn't correspond to the actual error.

|||

As you noticed, RAISERROR is NOT allowed in a FUNCTION.

A FUNCTION cannot have any output EXCEPT the FUNCTION value. Of course, it will 'crash' when unacceptable actions occur, such as divide by zero.

Louis indicated an acceptable method of handling a data anomaly.

|||

Thanks....

I'll probably use:

Code Snippet

if @.@.rowcount =0

select 1/0

No comments:

Post a Comment