SQL存储过程分页

SQL存储过程分页，拼接SQL语句方法

CREATE PROCEDURE [dbo].[proc_TestDemo01]
(
    @StartIndex int, --起始行号
    @EndIndex int, --结束行号
    @StartDate VARCHAR(50),
    @EndDate VARCHAR(50),
    @UserName NVARCHAR(50)
)

AS
DECLARE @wSql VARCHAR(1000) --用来保存条件语句
DECLARE @strSQL VARCHAR(3000) --用来保存SQL语句
BEGIN
    SET @wSql= ''
    IF Len(@UserName)>0
        SET @wSql=@wSql+' AND [UserName] like ''%'+@UserName+'%'''
    SET @strSQL='
    WITH list As(Select ROW_NUMBER() OVER (ORDER BY [Id])AS Row
        ,Id
        ,UserName
        ,Address
        ,Phone
    FROM UserListTable t
    WHERE (
            [BirthDay] >= Convert(Datetime,''' + @StartDate + ' 00:00:00'') AND [BirthDay] <= Convert(Datetime,''' + @EndDate + ' 23:59:59''))
            )
        '+@wSql+' 
    )
    
    Select (Select Count(0) From list) As RecordCount,* From list Where Row Between '+CONVERT(VARCHAR(10), @StartIndex)+' and '+Convert(varchar(10),@EndIndex)+' Order By Row
    '
    EXEC(@strSQL)

End