SQL Server paging query general stored procedure (only for paging query)

Time:2021-10-22

It has been used since the beginning of the project. The creator of this stored procedure (sorry, forgot his name) wrote this SQL code very well. On this basis, I adjusted the code according to my habits and mode of thinking, and only used it for paging query.

/*----------------------------------------------
*procedure name : P_PageResult
* author :Fay
* create date : 2014-07-18
*/
CREATE PROCEDURE prcPageResult
--Get data for a page--
@Currpage int = 1, -- current page number (i.e. top currpage)
@Showcolumn varchar (2000) = '*' -- required fields (i.e. column1, column2,......)
@Tabname varchar (2000), -- the table name to be viewed (i.e. from table_name)
@Strcondition varchar (2000) = '', - query criteria (i.e. where condition...) do not need to add the where keyword
@Asccolumn varchar (100) = '', - sorted field name (i.e. order by column ASC / DESC)
@Bitordertype bit = 0, -- sort type (0 is in ascending order and 1 is in descending order)
@Pkcolumn varchar (50) = '', - primary key name
@PageSize int = 20 -- page size

AS
Begin -- stored procedure start
--Several variables required by the stored procedure--
DECLARE @strTemp varchar(1000)
Declare @ strSql varchar (4000) -- the last statement executed by the stored procedure
Declare @ strordertype varchar (1000) -- sort type statement (order by column ASC or order by column DESC)

BEGIN
If @ bitordertype = 1 -- bitordertype = 1, the descending order is executed
BEGIN
SET @strOrderType = ' ORDER BY '[email protected]+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '[email protected]+' ASC'
SET @strTemp = '>(SELECT max'
END

If @ currpage = 1 -- if it is the first page
BEGIN
IF @strCondition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '[email protected]+' FROM '[email protected]+
' WHERE '[email protected][email protected]
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '[email protected]+' FROM '[email protected][email protected]
END

Else -- other pages
BEGIN
IF @strCondition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '[email protected]+' FROM '[email protected]+
' WHERE '[email protected]+' AND '[email protected][email protected]+'('[email protected]+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+
' '[email protected]+' FROM '[email protected][email protected]+') AS TabTemp)'[email protected]
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '[email protected]+' FROM '[email protected]+
' WHERE '[email protected][email protected]+'('[email protected]+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' '[email protected]+
' FROM '[email protected][email protected]+') AS TabTemp)'[email protected]
END

END
EXEC (@strSql)
End -- end of stored procedure
------------------------------------------------
GO

Call method:

prcPageResult 1,’*’,’TableName’,”,’CreateDate’,1,’PkID’,25

The above shows that the first 25 records of all fields in the query table tablename are the first page, the sorting field is createDate, in descending order, and the primary key is PKID. The function of this stored procedure is relatively powerful, which is very applicable in the project. If you don’t believe it, you can have a try, especially in the case of millions of data. Of course, this code can be converted into stored procedures in MySQL. However, it won’t be given to you here. You can try to convert it yourself.

The number of records in the following stored procedure query table:

/*----------------------------------------------
*procedure name : prcRowsCount
* author :Fay
* create date : 2014-07-18
*/
CREATE PROC prcRowsCount
@Tabname varchar (200), -- table name to query
@Colname varchar (200) = '*' -- column name to query
@Condition varchar (200) = '' -- query condition
AS
BEGIN
DECLARE @strSql varchar(255)
IF @condition = ''
SET @strSql='select count('[email protected]+') from '[email protected]
ELSE
SET @strSql='select count('[email protected]+') from '[email protected]+' where '[email protected]
EXEC (@strSql)
END
------------------------------------------------
GO

There are also general stored procedures for deleting records and general stored procedures for querying a single record. Thank you for providing the original general paging query stored procedures. Thank you.