GO
/****** 对象: StoredProcedure [dbo].[GetRecordFromPage] 脚本日期: 03/31/2010 16:59:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetRecordFromPage]
@OutputOne varchar(2000) output, --输出参数1
@OutputTwo varchar(2000) output, --输出参数2
@iTotalPage int = 0 output, --总页数
@iAllCount int = 0 output, --记录总数
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 查询字段集
@strWhere varchar(1000) = '' , -- 查询条件(注意: 不要加where)
@OrderType nvarchar(200), -- 设置排序类型, 非0 值则降序
@PageIndex int = 1 , -- 当前页码
@PageSize int = 10 -- 每页显示记录数
AS
declare @strSQL varchar(1000) -- 主语句
declare @strCountSQL varchar(255) -- 记录总数
declare @strTmp varchar(300) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @tmpSql nvarchar(1000) -- 临时sql
--处理总记录数,和总页数
--if @IsCount != 0
if @strWhere != ''
set @tmpSql='select @iAllCount = count(*) from '+@tblName+ ' where '+@strWhere --为变量赋值--执行@sql中的语句
else
set @tmpSql='select @iAllCount = count(*) from '+@tblName --为变量赋值--执行@sql中的语句
select @OutputTwo = @tmpSql
exec sp_executesql @tmpSql
,N'@iAllCount int OUTPUT' --表示@sql中的语句包含了一个输出参数
,@iAllCount OUTPUT --和调用存储过程差不多,指定输出参数值print @user
if @iAllCount%@PageSize=0
set @iTotalPage=CEILING(@iAllCount/@PageSize)
else
set @iTotalPage=CEILING(@iAllCount/@PageSize)+1
--判断@PageIndex(当前页)
if @PageIndex<=0
begin
set @PageIndex=1
end
else
if @PageIndex > @iTotalPage
begin
set @PageIndex=@iTotalPage
end
if (charindex('ASC',@OrderType) > 0)
begin
if (@strWhere='' or @strWhere = NULL)
set @strSQL = 'Select * FROM (select ROW_NUMBER() Over(order by ' + Replace(REPLACE(@OrderType, ' ASC', ' '), ' DESC' , ' ') + ' ASC ) as rowId,' + @fldName + ' from ' + @tblName
else
set @strSQL = 'Select * FROM (select ROW_NUMBER() Over(order by ' + Replace(REPLACE(@OrderType, ' ASC', ' '), ' DESC' , ' ') + ' ASC ) as rowId,' + @fldName + ' from ' + @tblName + ' where ' + @StrWhere
end
if (charindex('DESC',@OrderType) > 0)
begin
if (@strWhere='' or @strWhere = NULL)
set @strSQL = 'Select * FROM (select ROW_NUMBER() Over(order by ' + Replace(REPLACE(@OrderType, ' ASC', ' '), ' DESC' , ' ') + ' DESC ) as rowId,' + @fldName + ' from ' + @tblName
else
set @strSQL = 'Select * FROM (select ROW_NUMBER() Over(order by ' + Replace(REPLACE(@OrderType, ' ASC', ' '), ' DESC' , ' ') + ' DESC ) as rowId,' + @fldName + ' from ' + @tblName + ' where ' + @StrWhere
end
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@PageIndex-1) * @PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @strSQL = @strSQL + ') as #temp where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
--set @strCountSQL = 'select count(*) as Total from [' + @tblName + ']'
--set @tmpSql=@strSQL
--print @pageIndex
--print @iTotalPage
--print @iAllCount
--print @strSQL
--return @strSQl
--print @iAllCount
select @OutputOne = @strSQL
Exec (@strSQL)