|
Posted on
2011-11-04 17:50
旅行者1号
阅读( 151)
评论()
编辑
收藏
举报
Create PROCEDURE SP_Pagination |
*************************************************************** |
*************************************************************** |
3.Sort :排序语句,不带 Order By 比如:NewsID Desc ,OrderRows Asc |
7. Group : Group 语句,不带 Group By |
***************************************************************/ |
@PrimaryKey varchar (100), |
@Sort varchar (200) = NULL , |
@Fields varchar (1000) = '*' , |
@Filter varchar (1000) = NULL , |
@ Group varchar (1000) = NULL |
IF @Sort IS NULL or @Sort = '' |
DECLARE @SortTable varchar (100) |
DECLARE @SortName varchar (100) |
DECLARE @strSortColumn varchar (200) |
DECLARE @operator char (2) |
DECLARE @type varchar (100) |
IF CHARINDEX( 'DESC' ,@Sort)>0 |
SET @strSortColumn = REPLACE (@Sort, 'DESC' , '' ) |
IF CHARINDEX( 'ASC' , @Sort) = 0 |
SET @strSortColumn = REPLACE (@Sort, 'ASC' , '' ) |
IF CHARINDEX( '.' , @strSortColumn) > 0 |
SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX( '.' ,@strSortColumn)) |
SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX( '.' ,@strSortColumn) + 1, LEN(@strSortColumn)) |
SET @SortName = @strSortColumn |
Select @type=t. name , @prec=c.prec |
FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype |
Where o. name = @SortTable AND c. name = @SortName |
IF CHARINDEX( 'char' , @type) > 0 |
SET @type = @type + '(' + CAST (@prec AS varchar ) + ')' |
DECLARE @strPageSize varchar (50) |
DECLARE @strStartRow varchar (50) |
DECLARE @strFilter varchar (1000) |
DECLARE @strSimpleFilter varchar (1000) |
DECLARE @strGroup varchar (1000) |
SET @strPageSize = CAST (@PageSize AS varchar (50)) |
SET @strStartRow = CAST (((@CurrentPage - 1)*@PageSize + 1) AS varchar (50)) |
IF @Filter IS NOT NULL AND @Filter != '' |
SET @strFilter = ' Where ' + @Filter + ' ' |
SET @strSimpleFilter = ' AND ' + @Filter + ' ' |
SET @strSimpleFilter = '' |
IF @ Group IS NOT NULL AND @ Group != '' |
SET @strGroup = ' GROUP BY ' + @ Group + ' ' |
DECLARE @SortColumn ' + @type + ' |
SET ROWCOUNT ' + @strStartRow + ' |
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + ' |
SET ROWCOUNT ' + @strPageSize + ' |
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + ' |
|
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步