Mssql common paging Stored Procedure instance

A useful general paging storage process can be used in any development. You only need to make a small change. If you need it, you can refer to this instance.

A useful general paging storage process can be used in any development. You only need to make a small change. If you need it, you can refer to this instance.

The Code is as follows:

/* General Stored Procedure */ USE existing managementsystem GO If exists (SELECT * FROM sys. objects where name = 'cndoup _ GetPageOfRecords ') Drop procedure cndoup_GetPageOfRecords GO -- Create a stored procedure Create procedure cndoup_GetPageOfRecords @ PageSize int = 20, -- page size @ CurrentPage int, -- page number @ Columns varchar (1000) = '*', -- the expected field @ TableName varchar (100), -- the table to be queried @ Condition varchar (1000) = '', -- Query condition, where keyword not required @ AscColumn varchar (100) = '', -- Name of the sorted field (that is, order by column asc/desc) @ BitOrderType bit = 0, -- sort type (0 is ascending, 1 is descending) @ PkColumn varchar (50) = ''-- primary key name AS BEGIN -- starts the Stored Procedure DECLARE @ strTemp varchar (300) DECLARE @ strSql varchar (5000) -- 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 -- descending order BEGIN SET @ strOrderType = 'order BY desc' SET @ strTemp = '<(SELECT min' END ELSE -- Ascending BEGIN SET @ strOrderType = 'order by asc' SET @ strTemp = '> (SELECT max' END IF @ currentPage = 1 -- first page BEGIN IF @ condition! ='' SET @ strSql = 'select TOP '+ STR (@ pageSize) + 'from + 'Where ELSE SET @ strSql = 'select TOP '+ STR (@ pageSize) +' FROM END ELSE -- other pages BEGIN IF @ condition! ='' SET @ strSql = 'select TOP '+ STR (@ pageSize) + 'from + 'Where and from (select top '+ STR (@ currentPage-1) * @ pageSize) + 'From) AS TabTemp) '+ @ strOrderType ELSE SET @ strSql = 'select TOP '+ STR (@ pageSize) + 'from + 'Where FROM (select top '+ STR (@ currentPage-1) * @ pageSize) +' + 'From) AS TabTemp) '+ @ strOrderType END END EXEC (@ strSql) END -- End of Stored Procedure -- Obtain the room information list by page. EXEC cndoup_GetPageOfRecords 20, 2, 'room number = RoomNum, Room status = (SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID = Room. RoomTypeID ), Room status = (SELECT RSDec FROM RoomStatus WHERE RoomStatusID = Room. RoomStatusID ), Number of beds = BedNum, Floor = Floors, Description = RoomDes, Remarks = roomremark', 'room ', '', 'roomid', 0, 'roomid' -- Obtain the room information test based on the room number EXEC cndoup_GetPageOfRecords, 'room No. = RoomNum, Room status = (SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID = Room. RoomTypeID ), Room status = (SELECT RSDec FROM RoomStatus WHERE RoomStatusID = Room. RoomStatusID ), BedNum, Floors, RoomDes, Roomremark', 'room ', 'roomnum = 304', 'roomid', 0, 'roomid'