Hello everybody,
I wrote a stored procedure for SqlServer 2000 and i am using it for paging purpose.
The procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[OLAP_PagedRows]
(
@.SelectFields nVarchar(2000) =NULL,
@.GroupByFields nvarchar(1000) =NULL,
@.BaseTable varchar(100),
@.KeyColumn nvarchar(200)=NULL ,
@.JoinTables varchar(500) =NULL,
@.ConditionalClause varchar(1000) =NULL,
@.Pagesize int = 10,
@.PageNumber int =1,
@.SortExpression nvarchar(200)=NULL,
@.SearchText nvarchar(200)=NULL
)
AS
BEGIN
DECLARE @.SQLSTMT NVarchar(4000)
DECLARE @.SQLSTMT1 NVarchar(4000)
SET @.SQLSTMT1 = ''
--check whether page size is given null or not, if so set to default value
IF @.Pagesize IS NULL OR @.Pagesize = ''
BEGIN
SET @.Pagesize =10
END
--check whether page number is given null or not, if so set to default value
IF @.PageNumber IS NULL OR @.PageNumber = ''
BEGIN
SET @.PageNumber =1
END
--Start constructing the query --
SET @.SQLSTMT = 'SELECT '
SET @.SQLSTMT1 = 'DECLARE @.CountValue INT SELECT @.CountValue = count(*) From '+@.BaseTable
SET @.SQLSTMT = @.SQLSTMT + @.SelectFields + ' FROM '+@.BaseTable
If @.JoinTables Is Not Null
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' ' +@.JoinTables
SET @.SQLSTMT1 = @.SQLSTMT1 + ' ' +@.JoinTables
END
DECLARE @.StmtWhereClause nvarchar(500)
SET @.StmtWhereClause =''
Get where conditional clause
If (@.SearchText Is Not Null AND RTRIM(LTRIM(@.SearchText))<>'')
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.SearchText
END
If @.ConditionalClause Is Not Null AND RTRIM(LTRIM(@.ConditionalClause))<>''
BEGIN
IF (@.StmtWhereClause <> '')
BEGIN
SET @.StmtWhereClause= @.StmtWhereClause + 'AND ' +@.ConditionalClause
END
ELSE
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.ConditionalClause
END
END
SET @.SQLSTMT = @.SQLSTMT + @.StmtWhereClause
SET @.SQLSTMT1 = @.SQLSTMT1 + @.StmtWhereClause
If @.GroupByFields Is Not Null And RTRIM(LTRIM(@.GroupByFields))<>''
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' Group By ' +@.GroupByFields
SET @.SQLSTMT1 = @.SQLSTMT1 + ' Group By ' +@.GroupByFields
END
IF @.SortExpression Is Not Null AND RTRIM(LTRIM(@.SortExpression))<>''
BEGIN
SET @.SortExpression = LTRIM(RTRIM(' Order By '+ @.SortExpression))
SET @.SQLSTMT = @.SQLSTMT +' '+ @.SortExpression
SET @.SQLSTMT1 = @.SQLSTMT1 +' '+ @.SortExpression
END
SET @.SQLSTMT1= @.SQLSTMT1+' SELECT @.CountValue As MyRows '
--SELECT @.SQLSTMT1
--SELECT @.SQLSTMT
DECLARE @.StartRow INT
SET @.SQLSTMT = ' DECLARE temp_Cursor CURSOR SCROLL FOR '+@.SQLSTMT
EXECUTE SP_EXECUTESQL @.SQLSTMT
Open temp_Cursor
DECLARE @.RowCount INT
SET @.RowCount = 1
SET @.startRow = (@.PageSize * (@.PageNumber-1))+@.RowCount
--SELECT @.startRow as 'Current Row'
WHILE @.RowCount <= @.PageSize
BEGIN
--Select @.StartRow 'as @.StartRow'
FETCH ABSOLUTE @.startRow From temp_Cursor
SET @.RowCount= @.RowCount+1
SET @.StartRow = @.startRow + 1
END
deallocate temp_Cursor
EXECUTE SP_EXECUTESQL @.SQLSTMT1
END
It is working fine but I have problem with this kind of paging. I need to load the whole data into the cursor and i have to fetch records. The problem is that my table's contains more than Half a million records in it. If I have to load each time this cursor it will be a very big problem on the server side.
Probably it may not be a best solution, but sqlserver 2000 cannot provide more help than this. If I use sub-query for this like using Top <Number> it adversly effecting the nature of the data retrieval.
One solution that I am thinking is Load cursor once and whenever some updations performed on those tables from which cursor is getting data should be automatically reflect the changes.
Is this possible? Please help me.
Regards
Andy Rogers
hi Andy Rogers
why you use cursor with more data?
please try do'nt use cursor.
you can use temp table for sorting data.
if your data wholud'nt changes, you can use static data.
order on the 0.5 millions records has overloading on the sql server 2000.
sort your data and then use that with SELECT TOP X for best performance.
good luck
I modified your code a little. They way you were doing it, returns 1 record set with 1 record for every record. This method returns all records in 1 recordset.
Basically it does a "SELECT TOP @.startRow+PageSize", so you get the smallest set from top to bottom, into a temp table, then deletes everything before the @.startRow and returns the rest. This is the best method I have found.
DECLARE @.SQLSTMT NVarchar(4000)
DECLARE @.SQLSTMT1 NVarchar(4000)
SET @.SQLSTMT1 = ''
--check whether page size is given null or not, if so set to default value
IF @.Pagesize IS NULL OR @.Pagesize = ''
BEGIN
SET @.Pagesize =10
END
--check whether page number is given null or not, if so set to default value
IF @.PageNumber IS NULL OR @.PageNumber = ''
BEGIN
SET @.PageNumber =1
END
DECLARE @.StartRow INT
SET @.startRow = (@.PageSize * (@.PageNumber-1))+ 1
--Start constructing the query --
SET @.SQLSTMT1 = 'DECLARE @.CountValue INT SELECT @.CountValue = count(*) From '+@.BaseTable
SET @.SQLSTMT = 'SELECT TOP ' + CAST(@.startRow+@.PageSize AS VARCHAR(10)) + ' IntRowNum = IDENTITY(int,1,1), '
SET @.SQLSTMT = @.SQLSTMT + @.SelectFields + ' INTO #temptable FROM '+@.BaseTable
If @.JoinTables Is Not Null
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' ' +@.JoinTables
SET @.SQLSTMT1 = @.SQLSTMT1 + ' ' +@.JoinTables
END
DECLARE @.StmtWhereClause nvarchar(500)
SET @.StmtWhereClause =''
Get where conditional clause
If (@.SearchText Is Not Null AND RTRIM(LTRIM(@.SearchText))<>'')
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.SearchText
END
If @.ConditionalClause Is Not Null AND RTRIM(LTRIM(@.ConditionalClause))<>''
BEGIN
IF (@.StmtWhereClause <> '')
BEGIN
SET @.StmtWhereClause= @.StmtWhereClause + 'AND ' +@.ConditionalClause
END
ELSE
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.ConditionalClause
END
END
SET @.SQLSTMT = @.SQLSTMT + @.StmtWhereClause
SET @.SQLSTMT1 = @.SQLSTMT1 + @.StmtWhereClause
If @.GroupByFields Is Not Null And RTRIM(LTRIM(@.GroupByFields))<>''
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' Group By ' +@.GroupByFields
SET @.SQLSTMT1 = @.SQLSTMT1 + ' Group By ' +@.GroupByFields
END
IF @.SortExpression Is Not Null AND RTRIM(LTRIM(@.SortExpression))<>''
BEGIN
SET @.SortExpression = LTRIM(RTRIM(' Order By '+ @.SortExpression))
SET @.SQLSTMT = @.SQLSTMT +' '+ @.SortExpression
SET @.SQLSTMT1 = @.SQLSTMT1 +' '+ @.SortExpression
END
SET @.SQLSTMT = @.SQLSTMT + ' DELETE FROM #temptable WHERE IntRowNum < ' + CAST(@.startRow AS VARCHAR(10)) + ' SELECT * FROM #temptable DROP TABLE #temptable '
SET @.SQLSTMT1= @.SQLSTMT1+' SELECT @.CountValue As MyRows '
--SELECT @.SQLSTMT1
--SELECT @.SQLSTMT
EXECUTE SP_EXECUTESQL @.SQLSTMT
EXECUTE SP_EXECUTESQL @.SQLSTMT1
