Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Doubt in FOREACH query in Stored Function?

 
joeice james
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I want to write a Stored Function which will return a list of value. For that i am using the FOR EACH query.. The following query is not running for me..

CREATE FUNCTION get_temp_users() RETURNING INTEGER;

DEFINE v_id INTEGER;

FOREACH temp_cursor FOR

select temp_id
into v_id
from a_user
where status = 'C';

RETURN v_id WITH RESUME;

END FOREACH

END FUNCTION;

This is not working for me. It is throwing some 'syntax error'. If i remove the FOREACH tag, the statement was executed..

Please help me..

Regards,
Joeice
 
krishnamoorthy kitcha
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi james

yes it is possible, the function should returned more values

CREATE function udf_multiplevalues( @param int,@param2 int)
returns @returntable table (column1 int ,column2 varchar(255),
column3 int,column4 char(1),column5 int,column6 int,column7 int)
as
begin

insert into @returnable(column1,column2,column3...column n)
select col,col2,....coln from tablename


return
end

James, I think that, it is working fine

Check and tell me your feed back regarding this query

Rgs
k.krishnamoorthy.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic