cursors in functions

[Sonia Gupta]

CREATE FUNCTION [dbo].[waste] () RETURNS table AS declare @a int declare @b int BEGIN declare cur cursor for select a1,a2 from a open cur fetch next from a into @a,@b while @@FETCH_STATUS =0 BEGIN SELECT @a as a1 , @b as a2 fetch next from a into @a , @b END close cur deallocate cur return END where is the bug lying?

Soniagupta1@yahoo.co.in Yahoo Messenger Id = soniagupta1

[Krish KP]

1. remove BEGINafter the Decleration of A & B 2. and include before before A & B

Regards KP

[Sonia Gupta]

CREATE FUNCTION [dbo].[waste] () RETURNS table AS declare @a int declare @b int declare cur cursor for select a1,a2 from a open cur fetch next from a into @a,@b while @@FETCH_STATUS =0 BEGIN SELECT @a as a1 , @b as a2 fetch next from a into @a , @b END close cur deallocate cur return GO Now it is saying incorect syntax near the keyword declare. return statement statement i scalar valued function must include an argument

Soniagupta1@yahoo.co.in Yahoo Messenger Id = soniagupta1

[Rocky]

youre declaring that the fn will return a table and look at the last line

Sonia Gupta wrote:

return GO

whats happening here??

Rocky You can't climb up a ladder with your hands in your pockets.

[neeraj_indianic]

You have not specified that which type of function you want to create means scalar valued function or table valued function. I suppose that you want to create table valued function. For that your code is wrong in the starting line. Look at the below code. Hope it will guide you. CREATE FUNCTION [dbo].[waste]() RETURNS @student1 TABLE ( student_rec int, St_name nchar(10) ) AS Begin declare @a int declare @b nchar(10) declare cur cursor for select Id,name from student open cur fetch next from cur into @a,@b while @@FETCH_STATUS =0 BEGIN INSERT @student1 values(@a,@b) --SELECT @a as a1 , @b as a2 fetch next from cur into @a , @b END close cur deallocate cur RETURN end Neeraj Gupta IndiaNIC Infotech Ltd.