USE [XZCDB]
GO
/****** Object: StoredProcedure [dbo].[NTP_PageXZC] Script Date: 11/12/2014 13:12:41 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[NTP_PageXZC]
@IndexField varchar(50)='id',
@AllFields varchar(2000)='*', --需要返回的列
@TablesAndWhere varchar(2000)='', -- 表名和条件,from后面的,不要from,要一条where
@OrderFields varchar(255)='',-- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@RecordCount int output,
@PageCount int output
AS
if @PageSize < 1
set @PageSize = 10
declare @strSQL nvarchar(4000) -- 主语句
set @strSQL = 'select @RecordCount=count('+ @IndexField +') from '+ @TablesAndWhere
exec sp_executesql @strSQL,N'@RecordCount int output',@RecordCount out
if @RecordCount % @PageSize = 0
set @PageCount = @RecordCount/@PageSize
else
set @PageCount = @RecordCount/@PageSize+1
if @PageCount<@PageIndex
set @PageIndex = @PageCount
if @PageIndex<0
set @PageIndex = 1
declare @P_begin int
declare @P_end int
set @PageIndex=@PageIndex-1
set @P_begin=@PageIndex*@PageSize
set @P_end=@P_begin+@PageSize
set @strSQL= 'select *,'+@IndexField+' from(select ROW_NUMBER() OVER ('+@OrderFields+') as Num,'+@AllFields+' from '+@TablesAndWhere+')as Tab where Tab.Num>'+CONVERT(varchar(10),@P_begin)+' and Tab.Num<='+CONVERT(varchar(10),@P_end)
exec sp_executesql @strSQL
GO