Database paging stored procedures, supporting multi-table joint queries, paging stored procedures

Database paging stored procedures, supporting multi-table joint queries, paging stored procedures

Database paging stored procedure, supporting multi-table joint Query

Create procedure [dbo]. [getInQuiryAllByPage] @ tableNames varchar (5000), -- table name, can be multiple tables, separated by commas @ tbFields varchar (5000) = '*', -- field name, if multiple tables have a prefix of @ conditionStr varchar (5000) = '', -- where clause, which can be null, without where @ sortedStr varchar (5000) or -- sorting fields, multiple can be entered, to include desc or asc, without order by, required, cannot be blank -- the sorting field cannot have null values, exclude null values in the where clause, or use the isnull function to resolve @ needCount bit = 1, -- whether to obtain the total number of records @ pageIndex int = 0, -- page index @ pageSize int = 10, -- page size @ recordCount int = 0 output, -- Total number of returned records @ pageCount int = 0 output -- total number of returned pages ASBEGINdeclare @ SQL nvarchar (4000) -- main SQL statement declare @ sortStr2 varchar (8000) -- order by clause declare @ sortStr3 varchar (8000) -- order by clause declare @ whereStr nvarchar (4000) -- condition declare @ sortStr nvarchar (4000) -- condition set @ sortStr = LOWER (@ sortedStr) set @ sortStr2 = REPLACE (@ sortStr, 'desc', '@ a @') set @ sortStr2 = REPLACE (@ sortStr2, 'asc ',' @ d @ ') set @ sortStr2 = REPLACE (@ sortStr2, '@ a @', 'asc ') set @ sortStr2 = REPLACE (@ sortStr2,' @ d @ ', 'desc ') -------------------------------------------------------------- set @ sortStr2 = REPLACE (@ sortStr2, 'a. ', '') set @ sortStr2 = REPLACE (@ sortStr2,' B. ', '') set @ sortStr2 = REPLACE (@ sortStr2, 'C. ', '') set @ sortStr2 = REPLACE (@ sortStr2, 'e. ', '') set @ sortStr2 = REPLACE (@ sortStr2, 'F. ', '') set @ sortStr2 = REPLACE (@ sortSt R2, 'G. ', '') set @ sortStr3 = @ sortStrset @ sortStr3 = REPLACE (@ sortStr3, 'a. ', '') set @ sortStr3 = REPLACE (@ sortStr3,' B. ', '') set @ sortStr3 = REPLACE (@ sortStr3, 'C. ', '') set @ sortStr3 = REPLACE (@ sortStr3, 'e. ', '') set @ sortStr3 = REPLACE (@ sortStr3, 'F. ', '') set @ sortStr3 = REPLACE (@ sortStr3, 'G. ', '') ---------------------------------------------------------------- set @ sortStr = 'ORDER BY' + @ sortStr set @ SortStr2 = 'ORDER BY' + @ sortStr2 set @ sortStr3 = 'ORDER BY' + @ sortStr3 if (@ conditionStr is not null and @ conditionStr! = '') Set @ whereStr = 'where' + @ conditionStr else set @ whereStr ='' -- if (@ needCound! = 0 or @ pageIndex = 0) -- The following shows the total number of records obtained. begin DECLARE @ R int SET @ SQL = 'select @ R = count (*) from '+ @ tableNames + @ whereStrEXEC SP_EXECUTESQL @ SQL, n' @ R int output', @ R OUTPUT SET @ recordCount = @ R set @ pageCount = (@ recordCount-1) /@ pageSize) + 1 end if (@ pageIndex <2) -- if the first page is begin set @ pageIndex = 1 set @ SQL = 'select top '+ str (@ pageSize) + ''+ @ tbFields + 'from' + @ tableNames + @ whereStr + @ sortStr; end else -- begin if (@ recordCount> @ pageIndex * @ pageSize) SET @ SQL = 'select * FROM ('+ 'select TOP' + STR (@ pageSize) + '* FROM (' + 'select top '+ STR (@ pageSize * @ pageIndex) + ''+ @ tbFields + 'from' + @ tableNames + @ whereStr + @ sortStr + ') as a' + @ sortStr2 + ') as B '+ @ sortStr3 else SET @ SQL = 'select * FROM (' + 'select top' + STR (@ recordCount-@ pageSize * (@ pageCount-1 )) + ''+ @ tbFields + 'from' + @ tableNames + @ whereStr + @ sortStr2 + ') AS a' + @ sortStr3 end print @ sqlEXEC SP_EXECUTESQL @ sqlEND