第一步：创建表
1 declare @countInt int 2 declare @age int 3 set @age =20 4 set @countInt=1 5 while(@countInt<10000) 6 begin 7 8 insert into student([sName],[sAge],[sAddress])values(‘彭‘+ convert(nvarchar(30),@countInt), 9 @age,‘中国北京‘) 10 11 set @countInt=@countInt+1; 12 set @age=@age+1; 13 if(@age>=50) 14 begin 15 set @age=25; 16 end 17 18 end 19 20 GO 21 drop table student 22 select * from student
第二步：创建存储过程
ALTER PROC [dbo].[SuperPage] ( /*传入参数*/ @TableName nvarchar(20), @TableField nvarchar(2000), --未用 @OrderBy nvarchar(200), @OrderByType int, @TableID nvarchar(200), @StrWhere nvarchar(2000), --未用 @TaoltCount int, @PageSize int, @CurrPage int, @returnVal int output ) AS DECLARE @ProcStrSQLCOUNT nvarchar(4000) DECLARE @ProcOrderBy nvarchar(200) DECLARE @returnCount int DECLARE @TranPageSuper nvarchar(50) IF(@OrderByType!=1) BEGIN SET @ProcOrderBy=‘ Order By ‘+@OrderBy+ ‘ DESC ‘; END ELSE BEGIN SET @ProcOrderBy=‘ Order By ‘+@OrderBy+ ‘ ASC ‘; END SELECT @TranPageSuper=‘MyTransaction‘ /*总条数*/ SET @ProcStrSQLCOUNT = ‘SELECT @returnCount=Count(1) FROM ‘+@TableName; BEGIN TRAN @TranPageSuper execute sp_executesql @ProcStrSQLCOUNT,N‘@returnCount int out‘,@returnCount out SET @returnCount=(@returnCount-1)/@PageSize+1 print @returnCount exec(‘ SELECT TOP ‘+@PageSize+‘ * FROM ‘+@TableName+‘ WHERE (‘+@TableID+‘ NOT IN (SELECT TOP (‘+@PageSize+‘*‘+@CurrPage+‘) ‘+@TableID+‘ FROM ‘ +@TableName + ‘ ‘+ @ProcOrderBy+‘)) ‘ +@ProcOrderBy) /*页数*/ SET @returnVal = @returnCount COMMIT TRAN @TranPageSuper --ROLLBACK TRAN @TranPageSuper
第三步：执行
DECLARE @return_value int, @returnVal int SELECT @returnVal = 0 EXEC @return_value = [dbo].[SuperPage] @TableName = N‘Student‘, @TableField = N‘*‘, @OrderBy = N‘sID‘, @OrderByType = 1, @TableID = N‘sID‘, @StrWhere = NULL, @TaoltCount = 0, @PageSize = 20, @CurrPage = 1, @returnVal = @returnVal OUTPUT SELECT @returnVal as N‘@returnVal‘ SELECT ‘Return Value‘ = @return_value GO