--解密存储过程
exec sp_decrypt 'p_page'
use Newt8c8
go
if exists(select * from dbo.sysobjects where name='p_page')
drop procedure p_page
go
/**分页的存储过程*/
create procedure p_page
(
@Tables varchar(1000), --表名,可以是多个表,但不能用别名
@PrimaryKey varchar(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields varchar(1000) = '*', --查询字段(多个表的字段),为空表示select *
@PageSize int = 15, --每页记录数
@CurrentPage int = 1, --当前页,表示第页
@Filter varchar(1000) = null,--条件,可以为空,不用填where
@Group varchar(1000) = null, --分组依据,可以为空,不用填group by
@Sort varchar(200) = null, --排序,默认按主键升序排列,不用填order by
@TotalPage int output --总页数
)
with encryption --加密
as
set nocount on
declare @intResult int
begin tran
declare @sql nvarchar(4000)
if @Filter is null or @Filter=''
set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from '
+ @Tables
else
set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from '
+ @Tables
+ ' where + ' + @Filter
exec sp_executesql @sql,N'@intResult int output',@intResult output
select @TotalPage= ceiling((@intResult+0.0)/@PageSize)