//=========================有输出的分页存储过程开始==================================================================
CREATE Proc PageShowYes
@PageSize int=10 ,--每页显示的记录数
@PageCurrent int=1 ,--当前要显示的页号
@FdName varchar(100)='' ,--主键名或者标识列名
@SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等
@OrderByStr varchar(1000)='',--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
@CountRows int=0 output, --返回记录总数
@CountPage int=0 output --返回总页数
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时
----------------------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+@OrderByStr
else
set @OrderBySqls = ''
--------
if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
--------
set @TmpStr = @WhereSqls
--如果显示第一页,可以直接用top来完成
if @PageCurrent<=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
goto LabelRes
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
LabelRes:
-----返回总记录数
set @Sqls = 'select @a=count(1) from '+@FromStr+@TmpStr
exec sp_executesql @sqls,N'@a int output',@CountRows output
-----返回总页数
if @CountRows <= @PageSize
set @CountPage = 1
else
begin
set @CountPage = @CountRows/@PageSize
if (@CountRows%@PageSize) > 0
set @CountPage = @CountPage + 1
end
return
GO
//=============================有输出,可修改的分页存储过程开始========================================================
CREATE Proc PageShowEdit
@PageSize int=10 ,--每页显示的记录数
@PageCurrent int=1 ,--当前要显示的页号
@FdName varchar(100)='' ,--主键名或者标识列名
@SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等
@OrderByStr varchar(1000)='',--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
@CountRows int=0 output, --返回记录总数
@CountPage int=0 output, --返回总页数
@EditFdName varchar(100)='', --要修改的字段的名称
@EditFdValue varchar(4000)='', --修改后字段的新值
@EditTableName varchar(1000)='' --要修改的字段所在的表名称(如果只查询一张表,可以省略此参数,参数自动赋值=@FromStr)
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时
----------------------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+@OrderByStr
else
set @OrderBySqls = ''
--------
if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
--------
set @TmpStr = @WhereSqls
--如果显示第一页,可以直接用top来完成
if @PageCurrent<=1
begin
select @Id1=cast(@PageSize as varchar(20))
----如果要修改当前页某字段的所有值,
if @EditTableName = ''
set @EditTableName = @FromStr
----
if @EditFdName <> ''
begin
set @Sqls = 'update '+@EditTableName +' set '+@FdName+' in(select top '+@Id1+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
exec(@Sqls)
end
-------------------
exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
goto LabelRes
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
----如果要修改当前页某字段的所有值,
if @EditTableName = ''
set @EditTableName = @FromStr
----
if @EditFdName <> ''
begin
set @Sqls = 'update '+@EditTableName +' set '+@FdName+' in(select top '+@Id1+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
exec(@Sqls)
end
-------------------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
LabelRes:
-----返回总记录数
set @Sqls = 'select @a=count(1) from '+@FromStr+@TmpStr
exec sp_executesql @sqls,N'@a int output',@CountRows output
-----返回总页数
if @CountRows <= @PageSize
set @CountPage = 1
else
begin
set @CountPage = @CountRows/@PageSize
if (@CountRows%@PageSize) > 0
set @CountPage = @CountPage + 1
end
return
GO
//-=======================无输出的分页存储过程开始=====================================================================
CREATE Proc PageShowNo
@PageSize int=10 ,--每页显示的记录数
@PageCurrent int=1 ,--当前要显示的页号
@FdName varchar(100)='' ,--主键名或者标识列名
@SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等
@OrderByStr varchar(1000)=''--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
----------------------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+@OrderByStr
else
set @OrderBySqls = ''
--------
if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
--------
--如果显示第一页,可以直接用top来完成
if @PageCurrent<=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
return
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
return
GO
//---------------------------------------------------------------------------------------------------------------------
Create procedure Pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 主键,分页必须用。
@fldSortName varchar(1000)='', -- 排序的字段名,多个名称用EN逗号隔开。
@PageSize int =10, -- 页尺寸
@PageIndex int =1, -- 页码
@doCount bit =0, -- 返回记录总数, 非 0 值则返回
@OrderType bit =0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
begin
--返回记录总数
if @doCount != 0
begin
if @strWhere !=''
set @strSQL ='select count(*) as Total from [' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
else
--不返回记录总数,返回结果集
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = 'order by [' + @fldName +'] desc'
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = 'order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ ' from [' + @tblName + '] where' + @strWhere +' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL ='select top ' + str(@PageSize,len(@PageSize)) +' ' +@strGetFields+' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top '+str((@PageIndex-1)*@PageSize) + '['+ @fldName + '] from [' + @tblName + ']'+@strOrder+')as tblTmp) '+ @strOrder
if @strWhere != ''
begin
set @strSQL ='select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
end
exec (@strSQL)
end
GO