Return multiple rows from a cursor with just one fetch
-
Suppose I have a table named partners with 240 rows. I need a stored procedure that will return rows 1-10 or 11-20 or 21-30 etc, such as the one that follows:
create procedure aa
@rowset integer
as
begin
SET NOCOUNT ON
DECLARE @pos integer
DECLARE cPartners SCROLL CURSOR FOR SELECT * FROM partners
OPEN cPartners
set @pos = @rowset * 10 + 1
FETCH ABSOLUTE @pos FROM cPartners -- Will return 1 row to client
CLOSE cPartners
DEALLOCATE cPartners
SET NOCOUNT OFF
end
GO
'Exec aa 1' will return just one row (the 11th). Instead of that, I need to return 10 rows. How can I do that? Any ideas?
Thanks,
Sakis Baziakos
P.S.
I tried 'set fetchbuffer 10' but it does not work in T-SQL. Or maybe it does and I don't do it correctly. Did I miss something out? Did I do something wrong?
P.S. 1
I know that I can fetch the results in variables, insert those variables in a temp table (or a table variable) and then select from the table, but I do not want to do this because I will have to declare the table and the variables. Instead, I need a more generic solution. Perhaps there is a way to insert fetched results directly in a temp table (something like 'select * into #tmp from ...').
P.S. 2
I know that I can dynamically create a temp table that will have all the columns I need plus an identity column, dynamically insert data in the table (using some exec(@sql) staement) and then select from the temp table only the rows I need (for example rows that have identity column between 11 and 20). Still, I don't want to do this either, because I want to avoid the overhead of creating the temp table and inserting all the rows in the temp table.
P.S. 3
Is there a way to open a cursor in one stored procedure (say sp xx) and from withing that stored procedure call another one (say sp yy) that will take as an argument the cursor of sp xx and execute a fetch on that -already open- cursor?
-
Try this link, seems to be doing what you want although admittedly I have not had a chance to test it. Syntax goes:
set cursor rows number for cursor_name
Link:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/51067;pt=51945
-
Thanks for your reply.
This seems to be working ok in T-SQL for Sybase, but not in T-SQL for SQL Server.
I am using SQL Server 2000...
-
You can do 10 fetches. But this will generate 10 result sets ...
:create procedure aa
@rowset integer
as
begin
SET NOCOUNT ON
DECLARE @pos integer, @co int
DECLARE cPartners SCROLL CURSOR FOR SELECT * FROM partners
OPEN cPartners
set @pos = @rowset * 10
set @co = 0
goto FETCHNEXT
while @co < 10 AND @@FETCH_STATUS = 0
begin
set @co = @co + 1
set @pos = @pos + 1
FETCHNEXT:
FETCH ABSOLUTE @pos FROM cPartners -- Will return 1 row to client
end
CLOSE cPartners
DEALLOCATE cPartners
SET NOCOUNT OFF
end
exec aa 2 gives :
i a b
----------- ---------- ----------
20 kaponi VINC
i a b
----------- ---------- ----------
21 mokeri MANU
i a b
----------- ---------- ----------
22 ramelo ARNA
i a b
----------- ---------- ----------
23 retimn PIER
i a b
----------- ---------- ----------
24 brere CLAU
i a b
----------- ---------- ----------
25 karlir JOEL
i a b
----------- ---------- ----------
26 lplus MARI
i a b
----------- ---------- ----------
27 barsa FUAD
i a b
----------- ---------- ----------
28 kafman ALFR
i a b
----------- ---------- ----------
29 dgdevi DIDI
i a b
----------- ---------- ----------
30 drut MICH
-
I know I can do that, but I want to produce just one result set.
Thanks anyway.
-
In SQL,
Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch.
-
I have a post in the other thread for this topic that should fit the bill for you. (message id = 106587)
-
Why don't you create a temp table to hold the rows you extract with the cursor. Once you have the rows you need, do your select from the temp table. Don't forget your best practice of manually dropping the temp table after you do the select.
-
I quote my P.S. 2 in my first post:
"I know that I can fetch the results in variables, insert those variables in a temp table (or a table variable) and then select from the table, but I do not want to do this because I will have to declare the table and the variables. Instead, I need a more generic solution. Perhaps there is a way to insert fetched results directly in a temp table (something like 'select * into #tmp from ...')."
Thanks.
-
Sorry, but I strongly disagree with the answers so far.
As you are obviously after some kind of paging, you have several alternatives, IMHO:
1 (and maybe best): If you only need to retrieve 240 rows, do this in one single SELECT....into an array of your front end app. It then is a very easy exercise for any procedural programming language.
2. Use dynamic sql to execute something like SELECT TOP xxx FROM ...ORDER BY...
3. Following 2. I would look into the script section here. Will give you multiple hits. You don't need to reinvent the wheel.
Unless I am totally mistaken there is absolutely no need for a cursor in this case.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
240 was just a random number. The fact is that I am looking for a generic way to implement paging. Not just on partners table. On any table with any number of rows. So, alternative 1 will not do.
In case of alternative 2, how can I fetch rows 41-50 using top? Select top 50 from partners where id_col not in (select top 40 from partners) is surely no solution. Keep in mind that another problem is that I have the rows ordered by some column other that the PK column.
As for the need for a cursor, I quote my P.S. 2
"I know that I can dynamically create a temp table that will have all the columns I need plus an identity column, dynamically insert data in the table (using some exec(@sql) statement) and then select from the temp table only the rows I need (for example rows that have identity column between 11 and 20). Still, I don't want to do this either, because I want to avoid the overhead of creating the temp table and inserting all the rows in the temp table."
So, the use of the cursor is an alternative I am currently testing while trying to implement paging. As I see it, it has the advantage of not inserting all the rows in the temp table, just fetching the ones needed.
As for paging, I would like to have dynamic order by clause, dynamic column list, dynamic where clause.
Sakis.
-
As I said earlier, what I really need to do is paging. I would like to have dynamic order by clause, dynamic column list, dynamic where clause
. Here is a typical part of a stored procedure that shows what I am currently doing:
........
........
select @StartRec = convert(varchar, @FromRec)
select @EndRec = convert(varchar, @ToRec)
---------------------------------------------------------------
-- Prepare column list, from clause, where clause, order by clause
if @ColSwitch = 1 select @cols = ' col1, col2'
if @ColSwitch = 2 select @cols = ' col1, col2, col3, col4'
if @ColSwitch = 3 select @cols = ' col5, col6, col7'
if @FromSwitch = 1 select @from = '
from partners'
if @FromSwitch = 2 select @from = '
from partners inner join ....'
if @WhereSwitch = 1 select @where = ' where partner_name = ''xxx'' and partner_city = ''rrr'' '
if @WhereSwitch = 2 select @where = ' where partner_city = ''zzz'' '
if @OrderSwitch = 1 select @OrderBy = ' order by col1, col2'
if @OrderSwitch = 2 select @OrderBy = ' order by col1, col3, col4'
---------------------------------------------------------------
-- Prepare dynamic SQL
set @que = ''
set @que = @que + 'select identity(int, 1, 1) as id_tmp, ' + @cols + 'into #tmp' + @from + 'where 1 = 2;'
set @que = @que + 'insert into #tmp select ' + @cols + @from + @where + @OrderBy + ';'
set @que = @que + 'select * from #tmp where id_tmp between ' + @StartRec + ' and ' + @EndRec + ';'
set @que = @que + 'drop table #tmp;'
-- Execute SQL
exec(@que)
The second command of the dynamic SQL is an insert that inserts ALL rows that were found in the temp table. The third command returns just the rows needed. As I see it, there is a waste of resources here. Why insert all rows if I only need rows 31-40 for example? So, my next idea was to use a cursor in a way such as the one that follows:
SET NOCOUNT ON
declare @sql varchar(1000)
exec('DECLARE cPartners CURSOR SCROLL FOR
select ' + @cols + @from + @where + @OrderBy )
OPEN cPartners
FETCH ABSOLUTE @FromRec FROM cPartners
CLOSE cPartners
DEALLOCATE cPartners
SET NOCOUNT OFF
where
@cols, @from, @where, @OrderBy, @FromRec are defined similar as above. This however returns only one row at a time. Can I make that return 10 rows at once? I do not want to execute another 9 'fetch next' commands because that will produce different result sets.
If the cursor will not work, I am open to any other possible solution. Keep in mind that the table may have ANY number of rows!
Sakis.
-
http://www.microsoft.com/sql/techinfo/tips/administration/resultset.asp
http://www.winnetmag.com/SQLServer/Article/ArticleID/40505/40505.html
http://www.aspfaqs.com/webtech/062899-1.shtml
http://www.15seconds.com/Issue/010308.htm
http://www.google.de/search?q=sql%2Bserver%2Bpaging&hl=de&lr=&ie=UTF-8&oe=UTF-8&start=10&sa=N
I haven't looked too much into these links, but none of them utilizes a cursor. If you still insist on the use of this thing, do as you pleased.
As I said in my first post to this topic:
You should go back to the basics! In a tiered architecture, display is done in the front end. The database backend simply retrieves data and returns it in a particular order to the next tier.
As for dynamic sql, dynamic searches and generic database object
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Good luck!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank,
I will take a look at those links.
Sakis.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply