SqlServer Stored Procedure 分页

USE [Store]
GO
/****** Object:  StoredProcedure [dbo].[PageList]    Script Date: 5/24/2018 10:03:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <YuanXin>
-- Create date: <Create Date,,>
-- Description:    <PageList>
-- =============================================
Create PROCEDURE [dbo].[PageList]
    -- Add the parameters for the stored procedure here
    @tb_name nvarchar(200),
    @strfld nvarchar(200),
    @strWhere varchar(500),
    @pageIndex int,
    @pageSize int,
    @sort varchar(255)

AS
    declare @strsql nvarchar(max)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    begin
        set @strsql = ' select * from (select row_number()
        over(order by ' + @sort + ') as rownum, ' + @strfld + ' from ' + @tb_name + ' where ' + @strWhere + ') as Dwhere
        where rownum between ' + cast(((@pageIndex-1)*@pageSize + 1) as nvarchar(20)) + ' and '+ cast((@pageIndex*@pageSize) as nvarchar(20))
    end
    exec(@strsql)
    set nocount off;
    
END