Stored Procedure Implementation paging, stored procedure Paging
USE [HDIS]
GO
/***** Object: StoredProcedure [dbo]. [AspNetPager] Script Date: 12/30/2014 09:00:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo]. [AspNetPager]
(@ Tablename nvarchar (1000), -- table name
@ Filedname nvarchar (4000), -- Query Field
@ StartIndex int, -- number of start records
@ EndIndex int, -- number of end records
@ Where nvarchar (4000), -- condition (does not contain where)
@ Orderfiled nvarchar (100), -- Sort field (CreateDate desc)
@ PageSize int,
@ PrmkeyName nvarchar (100 ),
@ PageIndex int,
@ Docount bit)
As
Begin
Declare @ date varchar (50), @ SQL nvarchar (4000), @ I int
Select @ date = CONVERT (nvarchar (50), serverproperty ('productversion '))
-- If (CONVERT (int, SUBSTRING (@ date,)> 8) ------ sql2000 or above
-- Begin
-- If (@ docount = 1)
-- Set @ SQL = 'select count (*) from '+ @ tablename + 'where' + @ where
-- Else
-- Begin
-- Set @ SQL ='
-- With temptbl (
-- SELECT ROW_NUMBER () OVER (order by '+ @ orderfiled +') AS Row, * from '+ @ tablename + 'where' + @ where + ')
-- SELECT '+ @ filedname +' FROM temptbl where Row between '+ CONVERT (nvarchar (100), @ startIndex) + 'and' + CONVERT (nvarchar (100), @ endIndex)
-- END
-- Exec (@ SQL)
-- End
-- Else
Begin ------- sql2000
If (@ docount = 1)
Set @ SQL = 'select count (*) from '+ @ tablename + 'where' + @ where
Else
Begin
Set @ I = CONVERT (nvarchar (100), @ PageSize) * (CONVERT (nvarchar (100), @ pageIndex)-1)
Set @ SQL = 'select TOP '+ CONVERT (nvarchar (100), @ PageSize) + '*
FROM '+ @ tablename + 'where (' + @ WHERE + 'and' + @ prmkeyName + 'NOT IN
(Select top '+ CONVERT (nvarchar (100), @ I) + ''+ @ prmkeyName +'
FROM '+ @ tablename + 'where' + @ WHERE + 'ORDER BY' + @ orderfiled +') ORDER by' + @ orderfiled
End
-- Print (@ SQL)
Exec (@ SQL)
End
End