SQL Stored Procedure Paging

ALTER PROCEDURE [dbo]. [BrokerToLenderDataShow2]
@Where VARCHAR (200),--the query condition does not contain ' Where ' characters, such as Id>10 and Len (userid) >9
[Email protected] VARCHAR (200),--sort does not contain ' order by ' characters, such as ID Asc,userid desc, you must specify ASC or DESC
-Note that when the @sorttype=3 is in effect, remember to add the primary key at the end, otherwise it will make you more depressed
[Email protected] INT,--collation 1: Positive order ASC 2: Reverse DESC 3: Multi-column Sorting method
[Email protected] INT,--Total record 0: Total record returned
@PageSize INT,--the number of records output per page
@PageIndex INT--current page, starting from 1
[Email protected] INT OUTPUT,--Remember to return the total record
[Email protected] INT OUTPUT--Returns the total number of pages

As
BEGIN
DECLARE @Sql VARCHAR, @Sql2 NVARCHAR (+), @sql3 NVARCHAR (+), @BetweenStart int, @BetweenEnd int

Set @BetweenStart = convert (int, @PageSize * @PageIndex) [email protected]+1
Set @BetweenEnd =convert (int, @PageSize * @PageIndex)

Set @Sql3 = ' select * ' From (select Row_number () over (ORDER by b.id) RowNumber,
* from loanee b where 1=1 ' [email protected]+ '
) T
where t.rownumber between ' + LTrim (str (@BetweenStart)) + ' and ' + LTrim (str (@BetweenEnd));
Print @sql3

EXEC (@sql3)

END

-----------------------------------------------------------------------------------

--No conditional call
exec [BrokerToLenderDataShow2] ", 10,1

--a call with a bar
exec [BrokerToLenderDataShow2] ' and id>10 ', 10,1

SQL Stored Procedure Paging