functions and varchar

[Danny Lewkin]

Hi List,

I spent some time making this function work :

CREATE OR REPLACE FUNCTION sp_max_GetTypelistId( tl_name VARCHAR )
RETURNS ULONG
BEGIN
DECLARE test ulong;
SET test = SELECT RecID FROM tbl_typelists WHERE LOWER(typelistname) =  
tl_name;
RETURN test;
END;

It always return NULL. If I run the query in sql editor it returns a  
value...
I tried several things, and at one point, I replaced the varchar also  
by a long and then I got a result...


CREATE OR REPLACE FUNCTION sp_max_GetTypelistId( tl_name LONG )
RETURNS ULONG
BEGIN
DECLARE test ulong;
SET test = SELECT RecID FROM tbl_typelists WHERE RecID = tl_name;
RETURN test;
END;


Totally ridiculous function of course, but this time, it returned the  
correct value!

Changing it to a third version :

CREATE OR REPLACE FUNCTION sp_max_GetTypelistId(tl_name LONG)
RETURNS VARCHAR
BEGIN
DECLARE test VARCHAR;
SET test = SELECT TYPELISTNAME FROM tbl_typelists WHERE RecID = tl_name;
RETURN test;
END;

it again returned NULL...
So I'm guessing that a function can't handle varchars very well..??
Any idea's?

Tanks,

Danny