SQL-UDF

[m1979]

I am trying to get the udf to give back a table but it will not work. I am new to this and i can't finde an answer to the problem i have.

Code:
-----
CREATE FUNCTION zufall (p_anzahl INTEGER, p_untereGrenze DOUBLE, p_obereGrenze DOUBLE)
RETURNS TABLE (nummer INTEGER, zahl DOUBLE)
BEGIN ATOMIC
DECLARE v_counter INTEGER DEFAULT 1;
DECLARE v_flag INTEGER DEFAULT 0;
DECLARE v_nummer INTEGER;
DECLARE v_zahl DOUBLE;

WHILE (v_counter <= p_anzahl) DO
SET v_nummer = v_counter;

WHILE v_flag = 0 DO
SET v_zahl = RAND();
IF v_zahl < p_untereGrenze OR v_zahl > p_obereGrenze THEN
SET v_flag = 0;
ELSE SET v_flag = 1;
END IF;
END WHILE;

SET v_counter = v_counter + 1;
END WHILE;
RETURN VALUES (v_nummer, v_zahl);
END
------

this function takes a 3 input parameters. like (3,0.1,0.9) and should give out a table with a counter that goes from 1-3 (first parameter) and the second value should be a random number between the second and third parameters.
but I don't know how to write the values so that they would be in a table....

thx for the help and sorry if it is a too simple question.

 

[blom0344]

I am wondering if this should not be a stored procedure instead of a user defined function?

I reckon an UDF should return just one set of results for each input instead of creating a whole table......

T. Blom
Information analyst
tbl@shimano-eu.com