CREATE PROCEDURE sp_page
@tb varchar(20),--表名 @col varchar(20),--按该列来进行分页 @coltype bit,--@col列的类型,0-数字类型,1-字符类型 @collist varchar(800),--要查询出的字段列表 @selecttype int,--查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页 @pagesize int,--每页记录数 @page int,--指定页 @minid varchar(50),--当前最小号 @maxid varchar(50)--当前最大号 AS DECLARE @sql varchar(8000) IF@coltype=1 BEGIN SET@minid='''' @minid '''' SET@maxid='''' @maxid '''' END SET@sql= CASE@selecttype WHEN1--前页 THEN'SELECT' @collist 'FROM(SELECTTOP' CAST(@pagesizeASvarchar) '' @collist 'FROM' @tb 'WHERE' @col '<' @minid 'ORDERBY' @col 'DESC)tORDERBY' @col WHEN2--后页 THEN'SELECTTOP' CAST(@pagesizeASvarchar) '' @collist 'FROM' @tb 'WHERE' @col '>' @maxid 'ORDERBY' @col WHEN3--首页 THEN'SELECTTOP' CAST(@pagesizeASvarchar) '' @collist 'FROM' @tb 'ORDERBY' @col WHEN4--末页 THEN'SELECT' @collist 'FROM(SELECTTOP' CAST(@pagesizeASvarchar) '' @collist 'FROM' @tb 'ORDERBY' @col 'DESC)tORDERBY' @col WHEN5--指定页 THEN'SELECTTOP' CAST(@pagesizeASvarchar) '' @collist 'FROM' @tb 'WHERE' @col 'NOTIN' '(SELECTTOP' CAST(@pagesize*(@page-1)ASvarchar) '' @col 'FROM' @tb 'ORDERBY' @col ')ORDERBY' @col END EXEC(@sql) GO |
|
来自: 仰望//45度微笑 > 《SQLServer》