By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,760 Members | 1,585 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,760 IT Pros & Developers. It's quick & easy.

Throwing errors in UDF scalar functions

P: n/a
I'm trying to create some User Defined Functions, which act as scalar
functions.

I can successfully create a UDF like this:
drop function arraylistset;
CREATE FUNCTION ArrayListSet (ArrayList VARCHAR(30), index integer, X
CHAR(1))
RETURNS VARCHAR(30)
LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
RETURN
(CASE
WHEN LENGTH(ArrayList) <= 1
THEN X
WHEN index = 0
THEN X || substr(ArrayList, 2)
WHEN index+1 = LENGTH(ArrayList)
THEN SUBSTR(ArrayList, 1,index) || X
ELSE SUBSTR(ArrayList, 1,index) || X || substr(ArrayList, index+2)
END)
;

....but what I haven't managed to do is put error checking and error
reporting into the function.
I would like to produce errors when index is out of bounds...

To do this I have tried:
- using a BEGIN/END SQL statement block into which could be embedded
SIGNAL calls. The Create Function does not seem to accept the use of
BEGIN/END, only RETURN.
- using a SIGNAL statement within a RETURN statement: again, error.

How do I throw an error in a scalar UDF?

Thanks,

Simon Riggs
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.