From cursor to table variable?

Short version:
I'm trying to write a UDF that does this:
INSERT INTO @.resultTable (intColumn, textColumn) FETCH NEXT FROM myCursor
Is that possible? Or do I need to fetch into local variables, then insert?
(I'd rather not, because you can't have a local variable of type "text".)
Thanks!
Long version:
I need to write a function that does the following:
1. Open a cursor
2. Fetch certain rows
3. Return those rows as a table
CREATE FUNCTION testFunc
RETURNS @.result TABLE (
intColumn int,
textColumn text
)
as
begin
-- declare & open cursor...
-- Here's what I want to do:
INSERT INTO @.result (intColumn, textColumn) FETCH NEXT FROM myCursor
-- Or do I have to do this:
DECLARE @.myInt int;
DECLARE @.myFakeText varchar(2000)
FETCH NEXT FROM myCursor INTO @.myInt, @.myFakeText
INSERT INTO @.result (intColumn, textColumn) VALUES (@.myInt, @.myFakeText)From your explanation, it sure sounds like you are taking some convoluted
solution path for some simple requirement.
Can you explain what exactly are you trying to do? Perhaps, if you could
post your table DDLs, sample data & expected results ( www.aspfaq.com/5006 )
it might be helpful.
Anith|||You're probably right! See my other post in this newsgroup, from 2:15 today,
called "Select rows @.start through @.end" -- how to do it?" for a description
of the larger problem. (news:#WX6VIu6FHA.3136@.TK2MSFTNGP09.phx.gbl)
Here's the short version:
I want to say "select the top 2000 rows, starting at row 15000, from myTable
order by foo". I know I could use SELECT TOP 17000... to limit the number of
rows returned, but I'm still pulling a ton more data than I need.
Thanks!
Jesse
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%2328vpsu6FHA.3276@.TK2MSFTNGP10.phx.gbl...
> From your explanation, it sure sounds like you are taking some convoluted
> solution path for some simple requirement.
> Can you explain what exactly are you trying to do? Perhaps, if you could
> post your table DDLs, sample data & expected results (
> www.aspfaq.com/5006 ) it might be helpful.
> --
> Anith
>|||This is not the way to write SQL; this is the procedural logic of a
magnetic tape file.
Actually, the description sounds like you need a VIEW and should not
be bothering with cursors (the mag tape file model of data) or the
table variables (1960's disk scratch file model of data).
In SQL we would not think about the PHYSICAL position of a row (which
is NOT anything like a record). We would use a predicate based on
relational keys that selects the desired subset. Operative word in
SQL: "set", not sequential files.|||> We would use a predicate based on
> relational keys that selects the desired subset.
We did use relational keys to select a desired subset. But since I can only
display a small fraction of it to the client at any given moment (i.e., one
web page worth), and I don't want to cache the full query results on the
application servers or pull all the data over the network then throw most of
it away, I need a way to select only a slice of my result set.
I'd call it the "2000's paginated output model of data".
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132180115.747023.278920@.g44g2000cwa.googlegroups.com...
> This is not the way to write SQL; this is the procedural logic of a
> magnetic tape file.
> Actually, the description sounds like you need a VIEW and should not
> be bothering with cursors (the mag tape file model of data) or the
> table variables (1960's disk scratch file model of data).
> In SQL we would not think about the PHYSICAL position of a row (which
> is NOT anything like a record). We would use a predicate based on
> relational keys that selects the desired subset. Operative word in
> SQL: "set", not sequential files.
>