Return multiple rows from a cursor with one fetch (need to do paging)
-
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?
-
Assuming you have a primary key or a unique index on the partners table, the solution is quite simple and doesn't involve using a cursor. If you order the query by the primary key or unique index columns you can then pass back the last returned key and use that in the query predicate. You can then use the SET ROWCOUNT feature to limit the rows returned. In the below example, I have added a second parameter to the procedure which would allow you to dynamically adjust the rows returned as the application desires. A value of 0 (zero) for the @ReturnBlockSize would allow you to return all the rows if necessary. The result set returned below would be the 21st through 40th rows.
SET NOCOUNT ON
go
IF EXISTS ( SELECT 1 FROM sysobjects WHERE id = object_id('dbo.partners') AND type = 'U')
DROP TABLE dbo.partners
go
CREATE TABLE partners( col1 int not null, col2 int not null)
go
CREATE UNIQUE INDEX idx1 ON partners(col1)
go
DECLARE @counter int
SET @counter = 1
WHILE @counter < 100
BEGIN
INSERT INTO partners (col1,col2) VALUES ( @counter, @counter )
SET @counter = @counter + 1
END
IF EXISTS ( SELECT 1 FROM sysobjects WHERE id = object_id('dbo.aa') AND type = 'P')
DROP PROCEDURE dbo.aa
go
CREATE PROCEDURE aa @LastRowKey integer, @ReturnBlockSize int
AS
SET NOCOUNT ON
SET ROWCOUNT @ReturnBlockSize
SELECT *
FROM partners
WHERE col1 > @LastRowKey
ORDER BY col1
SET ROWCOUNT 0
SET NOCOUNT OFF
GO
EXEC aa 20, 20
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp. -
Yes, I do have a primary key (PK) on the table. I have thought of your solution, but there are drawbacks. Namely:
1. My PK may be 2 or 3 or whatever number of cols. It won't be straightforward in that case.
2. My select clause already has an order by clause (even thought I deliberately left it out when submitting the post).
3. What happens when the client application request rows 1-10 and then rows 41-50?
To give you a more detailed idea of what is happening, I have a web application (ASP) that accesses data stored on an SQL Server 2000 and I need a generic, straightforward and cost effective (in terms of SQL Server performance) way to implement paging. I have already tried several techniques, but none of them left me satisfied. Take a look also at my P.S.
Thanks.
-
There is an article on this at 15 Seconds (http://www.15seconds.com/Issue/010308.htm) that will explain it fairly well (it was written for SQL 7 but this should work for 2000 as well).
The main point with paging using an SP involves dumping the desired data into a temp table with the first col being an identity (pk) for use in the paging process.
Hope this helps
-
I did not have this article in mind, however I have already implemented a similar solution.
The point remains that I want to avoid the overhead (in terms of processing)
1: of creating the temp table and
2: of inserting all the rows in the temp table (especially this !!!)
This is what led me to the solution of implementing a cursor.
Sakis
-
Well, if you do not want to implement a temp table or table variable, and you are not ordering by a unique index, I am not sure you can accomplish what you desire from within SQL Server via TSQL.
Have you considered other options such as coding a COM+ component in .NET to cache the data in an array that is populated in the order that you desire? If the partners tables is relatively static and not very large, this could be quite easily accomplished and coded to return an ADO recordset, or XML if you so desired. Randomly accessing the array in the component would be straight forward and the component could be coded to refresh itself every so often or if the table has a last modified data column the component could check for the max on that column and if it is later than the time the array was last refreshed it could perform another refresh. As a bonus, your app response time would improve as you wouldn't be constantly making calls to SQL Server.
Just a thought anyways. Sometimes us DBA's forget that there are ways outside of the DBMS to implement what we would like.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp. -
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. That includes tables that are not static and are very large.
Therefore, the scenario you are proposing is risky in terms of memory usage. In fact it would require a copy of the database within COM components. Even worse I would say, because I do have selects that retrieve data from multiple tables. So, I would need to cache the data for each select in COM components. I may have 10 tables, but I have 50, 60, 70, whatever number of selects.
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.
-
This may seem to simple, but why not place 'FETCH ABSOLUTE @FromRec FROM cPartners' into a loop?
DECLARE @MaxRec Int
SET @MaxRec = 10
WHILE (1=1)
BEGIN
FETCH ABSOLUTE @FromRec FROM cPartners
IF (@@FETCH_STATUS=-1) BREAK -- Exit end of loop
IF (@@FETCH_STATUS=-2) CONTINUE -- Next item
SET @FromRec = @FromRec + 1
IF (@FromRec >= @MaxRec) BREAK -- Exit end of loop
END
Regards,Coach James
-
Sakis,
You say that you end up with different results sets when using nine more FETCH NEXT's after the FETCH ABSOLUTE. I assume this is due to the ORDER BY clauses not including a set of columns which ensure uniqueness and therefore guarantee the order of the rows is the same for each execution. While nonuniqueness in the ORDER BY clause may appear to work under testing in some cases, the order is not ensured and could easily be disrupted by a change in the access plan the optimiser chooses or by performing index maintenance. If this is the case, even the solution provided by Coach James will not work. To be able to consistantly retrieve a result set where the rows are ordered exactly the same, the ORDER BY must include a column or set of columns which ensure uniqueness of the rows during the sort or in the index access path. If you can not do this, your efforts at retrieving the same 10 rows all the time will be in vain.
As for you question about whether you can fetch multiple rows at once, the following is from the SQL Books Online: "Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch." There for, if you wish to implement what you are looking for via a stored procedure, you must use a temporary table or table variable. Alternatively you could code and extended proc and use the block fetch capabilities of OLEDB to limit the result set size.
One option to ensure the same order each execution, that is possible if each table has a primary key, is to append the columns of the primary key to the order by clause. This can be done without changing the set of columns returned as long as the query is not a SELECT DISTINCT or includes a UNION. The following is from SQL BOL: "The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list."
The following is an example T-SQL only solution using the above functionality to perform paging of rows dynamically. It will work on an single table select where the table has a primary key. As you have not provided a real table shema, I have used the Employees table in the Northwind database as an example. You can modify as necessary to suit you needs. However, it will get a lot more complex if you are doing joins between tables. You can obviously code it to to accept a list of tables that have referential integrity defined between then and dynamically generate the join of the tables and add the primary keys of each table to the ORDER BY clause. I will leave that to you if that is what you wish.
This solution will work, but I would definitely not recommend it for a high usage site. If your tables are of any significant size, you will want to ensure that you will not be incurring significant sorting due to the unknown permutions of ORDER BY clauses and you also run the risk of constant full table scans if the WHERE clauses are not supported by indexes. I would use EXTREME caution if ever implementing this on a production system. Your most efficient solution would be to code a set of specific stored procedures each of which implements only one of the access paths. You could then wrap those with a master procedure which could parse the input parms and determine which underlying proc to call. This is alot safer and much easier to control. You could implement a few variations at first, and if user requests warrant, add new ones as required. There are many other issues as well when implementing this solution. One usual stumbling block is that, the dynamic SQL within the proc requires that the user running the proc has the proper permissions on the under lying table as the permissions on the proc or not inherited by the dynamic SQL.
SET NOCOUNT ON
go
IF EXISTS ( SELECT 1 FROM sysobjects WHERE id = object_id('dbo.prc_GetPageOfRows') AND type = 'P')
DROP PROCEDURE dbo.prc_GetPageOfRows
go
CREATE PROCEDURE prc_GetPageOfRows
@TableOwner sysname
, @TableName sysname
, @SelectCols varchar(500) -- comma seperated list of columns
, @WhereClause varchar(1000) -- text following the WHERE statement
, @OrderByCols varchar(500) -- comma seperated list of columns
, @PageNum int -- number of page to start resultset at ( BASE 1 )
, @PageSize int = 10 -- number of rows to return ( default = 10 )
AS
SET NOCOUNT ON
DECLARE @QualifiedTableName varchar(257)
, @StartRow int
, @SQLText varchar(6000)
, @ColName sysname
, @ColDataType varchar(100)
, @ColLength smallint
, @ColPrec smallint
, @ColScale smallint
, @ColVariablesList varchar(1000)
, @SelectColList varchar(1000)
, @OrderColList varchar(1000)
, @CurParsePos int
, @WorkingColList varchar(1000)
, @DeclareVarText varchar(1000)
, @ColNullablility varchar(8)
, @DoneInd bit
, @indid int
, @indkey int
, @FirstColInd bit
SET @QualifiedTableName = cast(@TableOwner as varchar(128)) + '.' + cast(@TableName as varchar(128))
SET @StartRow = (@PageNum * @PageSize) - (@PageSize - 1)
SET @CurParsePos = 0
SET @DoneInd = 0
SET @SelectColList = ''
SET @ColVariablesList = ''
SET @DeclareVarText = ''
SET @FirstColInd = 1
SET @WorkingColList = rtrim(ltrim(@SelectCols))
SET @SQLText = ' CREATE TABLE #PagedResult ( prRowId int '
WHILE (@DoneInd = 0)
BEGIN
IF charindex(',',@WorkingColList,1) = 0
BEGIN
SET @ColName = rtrim(ltrim(@WorkingColList))
SET @DoneInd = 1
END
ELSE
BEGIN
SET @ColName = rtrim(ltrim(substring(@WorkingColList,1,charindex(',',@WorkingColList,1)-1)))
SET @WorkingColList = rtrim(ltrim(substring(@WorkingColList,charindex(',',@WorkingColList,1)+1,len(@WorkingColList)-charindex(',',@WorkingColList,1))))
END
SET NOCOUNT ON
SELECT @ColDataType = st.name
, @ColLength = sc.length
, @ColPrec = sc.prec
, @ColScale = sc.scale
, @ColNullablility = CASE columnproperty(sc.id, sc.name, 'AllowsNull') WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM syscolumns sc, systypes st
WHERE sc.id = object_id(@QualifiedTableName)
AND sc.xusertype = st.xusertype
AND sc.name = @ColName
IF @FirstColInd = 0
BEGIN
SET @SQLText = @SQLText + ', '
SET @ColVariablesList = @ColVariablesList + ', @'
SET @SelectColList = @SelectColList + ', '
SET @DeclareVarText = @DeclareVarText + ', @'
END
ELSE
BEGIN
SET @SQLText = @SQLText + ', '
SET @ColVariablesList = @ColVariablesList + ' @'
SET @SelectColList = @SelectColList + ' '
SET @DeclareVarText = @DeclareVarText + ' @'
SET @FirstColInd = 0
END
SET @ColVariablesList = @ColVariablesList + @ColName
SET @SelectColList = @SelectColList + @ColName
SET @DeclareVarText = @DeclareVarText + @ColName
SET @SQLText = @SQLText + @ColName
SET @ColDataType = CASE
WHEN @ColDataType IN ('datetime','int','smallint','tinyint','bit')
THEN @ColDataType
WHEN @ColDataType IN ('char','varchar','binary','varbinary','float','real')
THEN @ColDataType + '(' + cast(@ColLength as varchar(4)) + ')'
WHEN @ColDataType IN ('nchar','nvarchar')
THEN @ColDataType + '(' + cast(@ColLength/2 as varchar(4)) + ')'
WHEN @ColDataType IN ('dec','numeric')
THEN @ColDataType + '('+ cast(@ColPrec as varchar(4))+ ', ' + cast(@ColScale as varchar(4)) + ')'
END
SET @SQLText = @SQLText + ' ' + @ColDataType
SET @DeclareVarText = @DeclareVarText + ' ' + @ColDataType
SET @SQLText = @SQLText + ' ' + @ColNullablility
END
SET @SQLText = @SQLText + ' ) '
SET @SQLText = @SQLText + ' DECLARE crsr_Rows SCROLL CURSOR FOR'
SET @SQLText = @SQLText + ' SELECT ' + @SelectColList
SET @SQLText = @SQLText + ' FROM ' + @QualifiedTableName
SET @SQLText = @SQLText + ' WHERE ' + @WhereClause
SET @SQLText = @SQLText + ' ORDER BY ' + @OrderByCols
-- Add PK columns to order by clause
SELECT @indid = si.indid
FROM sysobjects so
, sysindexes si
WHERE so.xtype = 'PK'
AND so.parent_obj = object_id(@QualifiedTableName)
AND si.id = so.parent_obj
AND si.name = so.name
SET @indkey = 1
WHILE @indkey <= 16 AND index_col(@QualifiedTableName, @indid, @indkey) IS NOT NULL
BEGIN
IF @indkey = 1 and @OrderByCols = ''
SET @SQLText = @SQLText + ' '
ELSE
SET @SQLText = @SQLText + ', '
SET @SQLText = @SQLText + index_col(@QualifiedTableName, @indid, @indkey)
SET @indkey = @indkey + 1
END
SET @SQLText = @SQLText + ' FOR READ ONLY'
SET @SQLText = @SQLText + ' DECLARE @reccnt int, ' + @DeclareVarText
SET @SQLText = @SQLText + ' SET @reccnt = 0'
SET @SQLText = @SQLText + ' OPEN crsr_Rows'
SET @SQLText = @SQLText + ' FETCH ABSOLUTE ' + cast(@StartRow as varchar(9)) + ' FROM crsr_Rows INTO ' + @ColVariablesList
SET @SQLText = @SQLText + ' WHILE (@@FETCH_STATUS=0 AND @reccnt < ' + cast(@PageSize as varchar(9)) + ' ) '
SET @SQLText = @SQLText + ' BEGIN'
SET @SQLText = @SQLText + ' SET @reccnt = @reccnt + 1'
SET @SQLText = @SQLText + ' INSERT INTO #PagedResult VALUES ( @reccnt,' + @ColVariablesList + ')'
SET @SQLText = @SQLText + ' FETCH NEXT FROM crsr_ROWS INTO ' + @ColVariablesList
SET @SQLText = @SQLText + ' END'
SET @SQLText = @SQLText + ' CLOSE crsr_Rows'
SET @SQLText = @SQLText + ' DEALLOCATE crsr_Rows'
SET @SQLText = @SQLText + ' SELECT ' + @SelectCols + ' FROM #PagedResult ORDER BY prRowId'
SET @SQLText = @SQLText + ' DROP TABLE #PagedResult'
EXEC (@SQLText)
go
EXEC prc_GetPageOfRows
@TableOwner = 'dbo'
, @TableName = 'Employees'
, @SelectCols = 'LastName,FirstName,City,HireDate'
, @WhereClause = '1=1'
, @OrderByCols = 'City'
, @PageSize = 100
, @PageNum = 1
EXEC prc_GetPageOfRows
@TableOwner = 'dbo'
, @TableName = 'Employees'
, @SelectCols = 'LastName,FirstName,City,HireDate'
, @WhereClause = '1=1'
, @OrderByCols = 'City'
, @PageSize = 5
, @PageNum = 1
EXEC prc_GetPageOfRows
@TableOwner = 'dbo'
, @TableName = 'Employees'
, @SelectCols = 'LastName,FirstName,City,HireDate'
, @WhereClause = '1=1'
, @OrderByCols = 'City'
, @PageSize = 5
, @PageNum = 2
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply