分页存储过程代码

/*
[email protected]/* */ 当前页
[email protected]/* */ 每页记录数
[email protected]/* */ 表名
[email protected]/* */ 主键（自动排序）
[email protected]/* */ 查询条件
1）空为 null
2）有查询条件不要带where
[email protected]/* */ ‘0’表示　desc ‘1’是asc
[email protected]/* */ 总页数
*/
create procedure Page
@currentpage int,@pagesize int,
@TableName varchar(30),@key varchar(30),
@where varchar(50),@order varchar(1),
@pageCount int ,@str varchar(450) output
as
begin
—————执行的sql语句————
declare @sql nvarchar(400),@ordreby nvarchar(200)
declare @tempsql1 varchar(200),@tempsql2 varchar(200)
—————记录总数—————–
declare @count int
—————临时变量————————
declare @temp1 int,@temp2 int

set @TableName=’ [email protected]/* */+’ ‘
set @key=’ [email protected]/* */+’ ‘

if @order=’0′
set @ordreby=’ order by [email protected]/* */+’desc’
else
set @ordreby=’ order by [email protected]/* */

if @where=’null’
set @sql=’select @count = count(*) from ‘+ @TableName
else
set @sql=’select @count = count(*) from ‘+ @TableName+’ where [email protected]/* */

[email protected]/* */ 付值([email protected]/* */ 在说明是output 内型)—————————
exec sp_executesql @sql,[email protected]/* */ int out’,@count out
————求总页数——————————
if (@[email protected]/* */)=0
set @[email protected]/* */[email protected]/* */
else
set @[email protected]/* */[email protected]/* */+1
———–判断显示当前页是否异常——————
if @currentpage>@pagecount
set @[email protected]/* */
if @currentpage<1
set @currentpage=1
———-记录数小于页面显示记录数—————–
if(@currentpage=1)
begin
if @where=’null’
set @where=’ ‘
else
set @where=’ where [email protected]/* */
set @sql = ‘select top’+ str(@pagesize)+’ * from [email protected]/* */[email protected]/* */[email protected]/* */
end
else
begin
/**//* —————desc———————-
[email protected]/* */
[email protected]/* */
*假设一共77个记录，每次取10个。取67～58（第2页），去掉前面的57（1～57）个和后面的10个（77～66）
*/
if @order=0
begin
set @temp1 = @[email protected]/* */[email protected]/* */
if @temp1<0
set @temp1=0
set @temp2 = (@currentpage – 1)[email protected]/* */
if @where=’null’
begin
set @tempsql1=’select top ‘ + str(@temp1)+’ [email protected]/* */+’ from ‘ + @TableName+’ order by ‘ [email protected]/* */
set @tempsql2=’select top ‘ + str(@temp2)+’ [email protected]/* */+’ from ‘ + @TableName + @ordreby
end
else
begin
set @tempsql1=’select top ‘ + str(@temp1)+’ [email protected]/* */+’ from ‘ + @TableName+’ where [email protected]/* */+’ order by ‘ [email protected]/* */
set @tempsql2=’select top ‘ + str(@temp2)+’ [email protected]/* */+’ from ‘ + @TableName+’ where [email protected]/* */[email protected]/* */
end
set @sql=’ select top ‘ + str(@pagesize) + ‘ * from ‘ + @TableName + ‘ where [email protected]/* */+ ‘ not in ‘
set @sql= @sql+’ ( ‘+ @tempsql1 +’ ) and ‘
set @sql= @[email protected]/* */+ ‘ not in ( [email protected]/* */ +’ ) ‘
if @where=’null’
set @sql= @[email protected]/* */
else
set @sql= @sql+’ and [email protected]/* */[email protected]/* */
end
/**//* —————-asc———————
* @temp 表示前面显示的记录总数
* 去掉 @temp 在取出 pagesize 个即可
*/
else
begin
set @temp1=(@currentpage-1)[email protected]/* */
if @where=’null’
set @tempsql1=’select top ‘+ str(@temp1)+’ [email protected]/* */+’ from ‘ + @TableName + @ordreby
else
set @tempsql1=’select top ‘+ str(@temp1)+’ [email protected]/* */+’ from ‘ + @TableName ++’ where [email protected]/* */[email protected]/* */
set @sql=’ select top ‘ + str(@pagesize) + ‘ * from ‘ + @TableName + ‘ where [email protected]/* */+ ‘ not in ‘
set @[email protected]/* */+’ ( [email protected]/* */+’ ) ‘
if @where=’null’
set @sql= @[email protected]/* */
else
set @sql= @sql+’ and [email protected]/* */[email protected]/* */

end
/**//* ————————————-*/
end
set @[email protected]/* */
–exec sp_executesql @sql

end

GO