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 fields
@startIndex int,--Number of start records
@endIndex int,--end record count
@where nvarchar (4000),--conditions (not including where)
@orderfiled nvarchar (100),--sort field (CreateDate desc)
@PageSize int,
@prmkeyName nvarchar (100),
@pageIndex int,
@docount bit)
As
Begin
DECLARE @date varchar, @sql nvarchar (4000), @i int
Select @date =convert (nvarchar (), serverproperty (' productversion '))
--if (CONVERT (int, SUBSTRING (@date, 0,3)) >8)------sql2000 or more
--Begin
--if (@docount =1)
--Set @sql = ' SELECT count (*) from ' + @tablename + ' where ' + @where
--Else
--Begin
--Set @sql = '
--With Temptbl as (
--SELECT Row_number () over (ORDER by ' + @orderfiled + ') as ROW, * from ' + @tablename + ' where ' + @where + ')
--SELECT ' + @filedname + ' from Temptbl where Row between ' +convert (nvarchar (+), @startIndex) + ' and ' +convert (nvarch AR (+), @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), @PageSize) * (CONVERT (nvarchar), @pageIndex)-1)
Set @sql = ' SELECT TOP ' + CONVERT (nvarchar, @PageSize) + ' *
From ' + @tablename + ' WHERE (' + @where + ' and ' + @prmkeyName + ' not in
(SELECT TOP ' +convert (nvarchar), @i) + ' + @prmkeyName + '
From ' + @tablename + ' WHERE ' + @where + ' ORDER BY ' + @orderfiled + ')] ORDER by ' + @orderfiled
End
--print (@sql)
EXEC (@sql)
End
End
Stored Procedure Implementation Paging