SQL Server paging query stored procedure

SQL Server paged query stored procedure used in the project.

"Stored Procedures"

Create PROCEDURE Prcpageresult

--Get the data of a page--
@currPage int = 1,--The current page number (that is, top currpage)
@showColumn varchar (2000) = ' * ',--required field (i.e. Column1,column2,......)
@strCondition varchar (2000) = ",--the query condition (i.e. where condition ...) no need to add Wherekeyword
@ascColumn varchar (100) = ',--Sort the field name (i.e. order by column Asc/desc)
@bitOrderType bit = 0,--type of sort (0 is ascending, 1 is descending)
@pkColumn varchar (50) = ",--primary Key name
@pageSize int = 20,--Paging size
@RecordCount int Output --Returns the total number of rows


As
Begin--The stored procedure starts
--A few variables that the stored procedure needs to use--
DECLARE @strTemp varchar (1000)
DECLARE @strSql varchar (4000)-The last statement that the stored procedure runs
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 is run descending
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--Assuming 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--The completion of the stored procedure
------------------------------------------------

GO

"Invocation Mode"

DECLARE @RecordCount int
EXEC prcpageresult 1, ' * ', ' ', ' id ', 1, ' id ', ten, @RecordCount out
SELECT @RecordCount

---

DECLARE @RecordCount int
EXEC prcpageresult 1, ' * ', ' name= ', ' Hugh ', ' id ', 0, ' id ', ten, @RecordCount out--The stored procedure relays the literal character as '. So name= ' Hugh ' correct wording should be ' name= ' Hugh ' ' instead of ' name=/' hugh/'
SELECT @RecordCount

SQL Server paging query stored procedure