Raising errors/Returning status val from UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Raising errors/Returning status val from UDF

Author

Topic

izaltsman
A custom title

1139 Posts

Posted - 2002-02-25 : 14:51:04

Hi all!

I am interested to find out how do people implement error-handling in UDFs. Particularly I'd be interested to know how would you deal with the following:

I have a UDF that performs some parameter and data validations and if
everything is validated successfully this function returns a table. However, if validation fails, I need to terminate processing and then somehow inform the calling procedure that the function has failed. So how do I report back a status value or raise a custom error to the calling stored procedure? As far as I can tell RAISERROR doesn't work with UDFs...

Any ideas, links and articles on error-handling in UDFs will be greatly apprecieated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-25 : 15:07:12

I had some SPs that would include a Status column in the output of the table (it always returned a recordset/resultset of some kind). Then I could check the status column:

CREATE PROCEDURE getResults @name varchar(30) AS
IF EXISTS (SELECT * FROM myTable WHERE name=@name)
BEGIN
SELECT 'Found' AS Status, * FROM myTable WHERE name=@name
RETURN
END
ELSE
BEGIN
SELECT 'Not Found' AS Status
RETURN
END

I'm not familiar with UDF's yet but you can convert this no problem. It's not a great example but you get the idea. Once it's a UDF you can test it in your calling code like this:

DECLARE @search varchar(30)
SELECT @search='Smith'
IF NOT EXISTS (SELECT * FROM UDFValidate(@search) WHERE Status='Found')
BEGIN
RAISERROR 'Search item "' + @search + '" not found', 16, 1
RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources