Mssql common paging stored procedure instance

The code is as follows:

Copy code

/* General paging 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' + @ ascColumn + 'desc' SET @ strTemp = '<(SELECT min' END ELSE -- Ascending BEGIN SET @ strOrderType = 'Order by' + @ ascColumn + 'asc' SET @ strTemp = '> (SELECT max' END   IF @ currentPage = 1 -- first page BEGIN IF @ condition! ='' SET @ strSql = 'SELECT TOP '+ STR (@ pageSize) + ''+ @ columns + 'from' + @ tableName + 'Where' + @ condition + @ strOrderType ELSE SET @ strSql = 'SELECT TOP '+ STR (@ pageSize) + ''+ @ columns + 'from' + @ tableName + @ strOrderType END   ELSE -- other pages BEGIN IF @ condition! ='' SET @ strSql = 'SELECT TOP '+ STR (@ pageSize) + ''+ @ columns + 'from' + @ tableName + 'Where' + @ condition + 'and' + @ pkColumn + @ strTemp + '(' + @ pkColumn + ') '+' FROM (select top '+ STR (@ currentPage-1) * @ pageSize) + ''+ @ PkColumn + 'from' + @ tableName + 'where' + @ condition + @ strOrderType + ') AS TabTemp)' + @ strOrderType ELSE SET @ strSql = 'SELECT TOP '+ STR (@ pageSize) + ''+ @ columns + 'from' + @ tableName + 'Where' + @ pkColumn + @ strTemp + '(' + @ pkColumn + ')' + 'FROM (select top' + STR (@ currentPage-1) * @ pageSize) + ''+ @ pkColumn + 'From' + @ tableName + @ strOrderType + ') 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'