SQL Server paging query Stored Procedure

SQL Server paging query stored procedures used in the project.

Stored Procedure]

Create PROCEDURE prcPageResult

-- Get data on a page --
@ CurrPage int = 1, -- current page number (that is, Top currPage)
@ ShowColumn varchar (2000) = '*', -- the expected field (column1, column2 ,......)
@ StrCondition varchar (2000) = '', -- the query condition (where condition...) does not need to be added with the where keyword.
@ 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
@ PageSize int = 20, -- page size
@ RecordCount int output -- total number of returned rows


AS
BEGIN -- starts the Stored Procedure
-- Several variables required for the stored procedure --
DECLARE @ strTemp varchar (1000)
DECLARE @ strSql varchar (4000) -- the last statement executed by the Stored Procedure
DECLARE @ strSql01 varchar (4000)
DECLARE @ strOrderType varchar (1000) -- sort type Statement (order by column asc or order by column desc)




BEGIN
IF @ bitOrderType = 1 -- bitOrderType = 1: Execute descending order
BEGIN
SET @ strOrderType = 'ORDER BY' + @ ascColumn + 'desc'
SET @ strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @ strOrderType = 'ORDER BY' + @ ascColumn + 'asc'
SET @ strTemp = '> (SELECT max'
END


IF @ currPage = 1 -- IF it is the first page
BEGIN
IF @ strCondition! =''
SET @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from dbo. demodata' +
'Where' + @ strCondition + @ strOrderType
ELSE
SET @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from dbo. demodata' + @ strOrderType
END


ELSE -- other pages
BEGIN
IF @ strCondition! =''
SET @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from dbo. demodata' +
'Where' + @ strCondition + 'and' + @ pkColumn + @ strTemp + '(' + @ pkColumn + ') '+' FROM (select top '+ STR (@ currPage-1) * @ pageSize) +
''+ @ PkColumn + 'FROM dbo. DemoData' + @ strOrderType + ') AS TabTemp)' + @ strOrderType
ELSE
SET @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from dbo. demodata' +
'Where' + @ pkColumn + @ strTemp + '(' + @ pkColumn + ')' + 'FROM (select top' + STR (@ currPage-1) * @ pageSize) + ''+ @ pkColumn +
'From dbo. demodata' + @ strOrderType + ') AS TabTemp)' + @ strOrderType
END

END

Select @ RecordCount = COUNT (*) from dbo. DemoData

EXEC (@ strSql)


END -- END of Stored Procedure
------------------------------------------------

GO

[Call method]

DECLARE @ RecordCount int
Exec prcPageResult 1, '*', ', 'id', 1, 'id', 10, @ RecordCount out
SELECT @ RecordCount

---

DECLARE @ RecordCount int
Exec prcPageResult 1, '*', 'name = 'hugh ''', 'id', 0, 'id', 10, @ RecordCount out -- specifies the bucket's escape delimiter ', therefore, the correct method for name = 'Hugh 'should be 'name =' hugh ''' instead of 'name =/'Hugh /''
SELECT @ RecordCount