How to perform error handling within functions

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 2005 Forums

Transact-SQL (2005)

How to perform error handling within functions

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-06 : 06:46:26

Hi All

I've written a basic function that turns a comma-separator nvarchar(max) max value into a table containing ID values.
I'd like to add a bit of error handling here to stop it being used incorrectly. Usually in SP's I do this with RAISERROR, but I see that this isn't allowed in a function.

Can anyone please enlighten me as to the best way to do error handling within a function. In this example, I'd like to check that the parameter value ends with a particular character, and if not, throw an exception...

CREATE FUNCTION myFn_createIdTable
(
@Ids nvarchar(max) = NULL,
@separator char(1) = ','
)
RETURNS @tbl_Ids TABLE
(
indexCol int Identity(1,1),
IdVal int NOT NULL
)
AS
BEGIN
-- check @Ids string ends with separator character
IF NOT RIGHT(@Ids, 1) = @separator
BEGIN
RAISERROR('Error message', 10, 1)
END
-- ... logic removed for now
RETURN
END
GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-06 : 07:18:03

I know that isn't your question but in this case I would do:
IF NOT RIGHT(@Ids, 1) = @separator
BEGIN
SET @Ids=@Ids + @separator
END