USE [data_smf]GO/****** 對象:? StoredProcedure [dbo].[page]??? 腳本日期: 01/10/2011 15:01:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:??<Author,,Name>-- Create date: <Create Date,,>-- Description:?<Description,,>-- =============================================ALTER PROCEDURE [dbo].[page]@key int=0, --返回總頁數(shù)或數(shù)據(jù)@value nvarchar(50)='-',--用于排序字段@size int=20, --每頁顯示數(shù)據(jù)的條數(shù)@order nvarchar(50)='asc',@table nvarchar(50),@column nvarchar(100)?AS ?SET NOCOUNT ON;--declare @KEYS intdeclare @sqlstr1 nvarchar(2000)declare @sqlstr2 nvarchar(2000)declare @sqlstr3 nvarchar(2000)--set @KEYS=@keyset @sqlstr1='select RowNumber=ceiling(count(id) *1.0/'+cast(@size as nvarchar(20))+')from ' +@table +' '--總頁數(shù)set @sqlstr2= 'select '+@column+' from (select row_number() over(order by '+@value+' '+@order+? ' ) as RowNum, *? from? '+@table+' ) t where t.RowNum between 20*('+cast(@key as nvarchar(20))+'-1) and 20*'+cast(@key as nvarchar(20))+'order by '+' '+@value+' '+@order+' ' --id可以被替換 BEGIN--返回總行數(shù)if(@key=0)beginexec(@sqlstr1)end--返回調(diào)用頁的數(shù)據(jù)?if(@key>0 and @value<>'-')begin? exec(@sqlstr2)end--返回總頁數(shù)，并且返回第一頁數(shù)據(jù)if(@key=-1)beginexec(@sqlstr1)-- 設(shè)置key 值無效set @key=1?exec(@sqlstr2) endEND ?