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
