USE [IYUEJIE]
GO
/****** Object: StoredProcedure [dbo].[CustomPageList] Script Date: 12/20/2013 11:42:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
CREATE PROCEDURE [dbo].[CustomPageList]
@tblName nvarchar(2000), -- 表名
@fldName nvarchar(2000), -- 主键字段名
@sortCondition nvarchar(2000), -- 主键字段名 id desc
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@ReCount int=0 output, -- 返回记录总数, 非 0 值则返回
@pageCount int=0 output, -- 返回记录条数
@strWhere nvarchar(4000) = '', -- 查询条件 (注意: 不要加 where)
@returnSql nvarchar(4000) output -----最后返回的SQL语句
AS
declare @reSQL nvarchar(2000)
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp nvarchar(2000) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare @strOrder nvarchar(2000) -- 排序类型
if @sortCondition is not null and @sortCondition != ''
begin
set @strOrder = ' order by ' + @sortCondition +''
end
SET @strSQL = ' Select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where '
+@fldName+' not in (Select top ' + str((@PageIndex-1)*@PageSize) + ' '
+@fldName+' from ' + @tblName +' ' + @strOrder +')' + @strOrder
if @strWhere is not null and @strWhere != ''
begin
SET @strSQL = ' Select top '+ str(@PageSize)+'* from ['+ @tblName + '] where '+@fldName+' not in (Select top ' + str((@PageIndex-1)*@PageSize)+@fldName+' from ' + @tblName +' where '+ @strWhere +''+ @strOrder+') and '+ @strWhere +' '+ @strOrder
end
if @PageIndex = 1
if @strWhere is not null or @strWhere<>''
begin
set @strTmp =''
if @strWhere is not null and @strWhere<>''
begin
set @strTmp = ' where ' + @strWhere
end
else
begin
set @strTmp =''
end
set @strSQL = ' select top ' + str(@PageSize) + '* from [' + @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @strWhere is not null and @strWhere<>''
set @reSQL = 'select @ReCount=count(*) from [' + @tblName + ']'+' where ' + @strWhere
else
set @reSQL = 'select @ReCount=count(*) from [' + @tblName + ']'
----取得查询结果总数量-----
exec sp_executesql @reSQL,N'@ReCount int out',@ReCount out
-----返回sql-----
set @returnSql =@strSQL
-----返回pageCount-----
if @PageSize>0
begin
if @ReCount%@PageSize=0
begin
set @pageCount=@ReCount/@PageSize
end
else
begin
set @pageCount=(@ReCount-(@ReCount%@PageSize))/@PageSize+1
end
end
--print @strSQL
exec (@strSQL)
GO