Please help: insert into table in cursor-loop?

Hi, all,

I'd like to insert returned items into the result table @.r:

Create function get_items

returns @.r table(a1 varchar(30),a2 varchar(30),a3 varchar(30),a4 varchar(30),a5 varchar(30))

as

begin

declare @.v_item nvarchar (30);

declare @.v_count int;

declare cur_items cursor for

select top 5 a.item from itemtable a; -- gets max. 5 items!

open cur_items;

fetch next from cur_items into @.v_item;

set @.v_count = 0;

while (@.@.fetch_status = 0)

begin

set @.v_count = @.v_count+1;

-- Problem:

-- Insert into @.r(a1,a2...) values(@.v_item)... ?

--

fetch next from cur_items into @.v_item;

end;

close cur_items;

DEALLOCATE cur_items;

return

END

=========

That means,

if @.v_count = 1,

@.r has only one item, such as: 'item1', <null>, <null>, <null>, <null>

but if @.v_count = 5,

@.r has full-row, such as: 'item1', 'item2', 'item3', 'item4', 'item5'

Thank you very much in advance!

If I understand your problem correctly, you don't need a cursor. Use a table valued function (TVF).

Something like this:

CREATE FUNCTION Get_Items ()
RETURNS table
AS
RETURN
( SELECT TOP 5 Item
FROM ItemTable
)

GO

|||

Hello, Arnie Rowland, thanks for your answer!

In my code I have to use the cursor, the definition of the cursor above is just example,

and the result from the cursor is: 'item1', 'item2'... or more, but max. 5 items.

Best regards

|||

Hi, all,

maybe I have to convert all rows of the result table to one row?

after execute the function I got e.g. 3 rows:

item1

item2

item3

==>

if I can convert them to one row, then I have:

item1 | item2 | item3 | <null> | <null>

How can I get it?

Best regards

|||

Here are some resources that may help you get your desired output.

I highly recommend NOT using a cursor if at all possible -and in almost all data retrieval situations, it is possible!

Lists -Field Concatenation
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

Lists -Field Concatenation( For SQL 2000 & 2005 )
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

Lists -Recursive Queries
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=9
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03i8.asp
http://www.wwwcoder.com/main/parentid/191/site/1857/68/default.aspx
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp