USE [tablename]
GO
/****** Object: StoredProcedure [dbo].[paginate] Script Date: 05/12/2013 17:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Jeremy Menethil>
-- Create date: <2013-04-03>
-- Description: <Best Paginate>
-- =============================================
ALTER PROCEDURE [dbo].[paginate]
-- Add the parameters for the stored procedure here
@tbName nvarchar(100),
@items nvarchar(500),
@where nvarchar(100),
@orderBy nvarchar(100),
@orderType int,
@pageSize int,
@pageCurrent int=1 output,
@pageCount int output,
@recordCount int output
AS
declare @strSql nvarchar(1000)
declare @strOrderType nvarchar(50)
declare @indexA int
declare @indexB int
declare @ab int
declare @str nvarchar(100)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--------Set pageSize
if @pageSize<1
Begin
set @pageSize=1
END
------------------------------
--------Set pageCureent
if @pageCurrent<1
Begin
set @pageCurrent=1
END
if @pageCurrent>@pageCount
Begin
set @pageCurrent=@pageCount
End
------------------------------
--------Set ordertype
if @orderType=1
Begin
set @strOrderType=' asc'
End
else
Begin
set @strOrderType=' desc'
End
------------------------------
--------Set Where
if @where!=''
Begin
set @where=' where '+@where
End
set @str='select @recordCount=COUNT(*) from '+@tbName+@where
exec sp_executesql @str,N'@recordCount int output,@tbName nvarchar(100)',@recordCount output,@tbName
--------Set pageCount
set @pageCount=@recordCount/@pageSize
if @recordCount%@pageSize>0
begin
set @pageCount=@pageCount+1
end
------------------------------
--------Execute Sql
set @indexA=(@pageCurrent-1)*@pageSize
set @indexB=@pageCurrent*@pageSize
set @strSql='select '+@items+' from (select ROW_NUMBER() over(order by '+@orderBy+@strOrderType+') as '
+'rowNum,* from '+@tbName+@where+') as tbTmp where rowNum >'
+str(@indexA)+' and rowNum <='+str(@indexB)
-- Insert statements for procedure here
exec(@strSql)
END