SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:阿瑞-- Create date: 2008-03-13
-- Description: 分页存储过程
-- Debug:exec Proc_AspNetPager 'ProductType','[PTypeId],[Name],[Order]','1=1',5,2,'[order]',1,0
-- =============================================
CREATE PROCEDURE [dbo].[Proc_AspNetPager]
@TableName varchar(20), --表明
@FieldName varchar(300), --显示的字段明
@Where varchar(500),
@PageSize int, --条数
@PageIndex int, --页码
@FiledOrder varchar(50), --排序字段
@Order bit, --排序类型 1为倒序，否则为正序
@DoCount bit --是否返回总数 1为返回,否则不返回
AS
BEGIN
DECLARE @SQL varchar(4000)
DECLARE @ORDERStr varchar(200)
SET NOCOUNT ON
IF(@Order=1)
SET @ORDERStr = 'ORDER BY '+@FiledOrder+' DESC '
ELSE
SET @ORDERStr = 'ORDER BY '+@FiledOrder+' ASC '
IF(@DoCount=1)
BEGIN
SET @SQL = 'SELECT COUNT(*) FROM '+@TableName+' WHERE '+@Where+' '
+ 'SELECT top '+cast(@PageSize as varchar)+' '+@FieldName+' FROM '+@TableName+' WHERE '+@Where+' '
+ @ORDERStr
END
ELSE
BEGIN
SET @SQL = 'WITH Temptbl as ('
+'SELECT ROW_NUMBER() OVER ('+@ORDERStr+')AS Row, '+@FieldName+' FROM '+@TableName+' WHERE '+@Where+')'
+'SELECT * FROM Temptbl WHERE Row between ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+1'+' and ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+'+cast(@PageSize as varchar)
END
EXEC(@SQL)
print @SQL
END