1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[db_article_GetArticlePagination] ( @pagesize int , @pageindex int , @classid int , @sp_keyes varchar (200), @isSmallClass bit ) AS SET NOCOUNT ON ; declare @filter varchar (4000) declare @tb varchar (50) declare @id varchar (50) set @tb= 'db_article' set @id= 'a_id ' if(@classid is null ) set @classid=0 if(@sp_keyes<> '' ) set @sp_keyes= ' and (sp_keyes LIKE ' '%' + @sp_keyes + '%' ')' else set @sp_keyes= '' if(@isSmallClass=0) set @filter= 'a_hidden=1 and ( ' + cast (@classid as varchar (20))+ '=0 OR classid= ' + cast (@classid as varchar (20))+ ' or classid IN (SELECT classid FROM db_sys_class WHERE nsort = ' 'article' ' AND c_class = ' + cast (@classid as varchar (20))+ ')) ' +@sp_keyes else set @filter= 'a_hidden=1 and ( ' + cast (@classid as varchar (20))+ '=0 OR classid = ' + cast (@classid as varchar (20))+ ')' +@sp_keyes --print @filter exec sp_Pagination @tb,@id, 'a_id desc' ,@pageindex,@pagesize, 'a_id,classid, username, a_topic, a_author, a_comto, a_pic, a_summary, a_counter, a_tim, a_arrtim' ,@filter, null |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_Pagination] ( @Tables varchar (1000), @PrimaryKey varchar (100), @Sort varchar (200) = NULL , @CurrentPage int = 1, @PageSize int = 10, @Fields varchar (1000) = '*' , @Filter varchar (1000) = NULL , @ Group varchar (1000) = NULL ) AS /*默认排序*/ IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey DECLARE @SortTable varchar (100) DECLARE @SortName varchar (100) DECLARE @strSortColumn varchar (200) DECLARE @operator char (2) DECLARE @type varchar (100) DECLARE @prec int /*设定排序语句.*/ IF CHARINDEX( 'DESC' ,@Sort)>0 BEGIN SET @strSortColumn = REPLACE (@Sort, 'DESC' , '' ) SET @operator = '<=' END ELSE BEGIN IF CHARINDEX( 'ASC' , @Sort) = 0 SET @strSortColumn = REPLACE (@Sort, 'ASC' , '' ) SET @operator = '>=' END IF CHARINDEX( '.' , @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX( '.' ,@strSortColumn)) SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX( '.' ,@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END 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) /*默认当前页*/ IF @CurrentPage < 1 SET @CurrentPage = 1 /*设置分页参数.*/ SET @strPageSize = CAST (@PageSize AS varchar (50)) SET @strStartRow = CAST (((@CurrentPage - 1)*@PageSize + 1) AS varchar (50)) /*筛选以及分组语句.*/ IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' ' SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @ Group IS NOT NULL AND @ Group != '' SET @strGroup = ' GROUP BY ' + @ Group + ' ' ELSE SET @strGroup = '' /*执行查询语句*/ EXEC ( ' 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助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步