以前写的一个分页存储过程 嗮一下

来源:互联网 发布:aws centos密码 编辑:程序博客网 时间:2024/05/16 18:35
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE  [dbo].[Proc_Paging]
@TableName nvarchar(50),@ReFieldsStr nvarchar(200)='*',@OrderStr nvarchar(200),
@WhereStr nvarchar(500)=N'',@PageSize int,@PageIndex int =0,
@TotalCount int out
AS
BEGIN
    SET NOCOUNT ON;
DECLARE @StartNum int;
DECLARE @EndNum int;
DECLARE @SqlStr nvarchar(2000);
DECLARE @TotalCountStr nvarchar(500);
SET    @StartNum=@PageIndex*@PageSize+1;
SET     @EndNum=@StartNum+@PageSize-1;
SET    @TotalCountStr=N'select @TotalCount=count(*) from '+@TableName;
SET     @SqlStr=N'(select row_number() over ( order by '+@OrderStr+')as rowId,'+@ReFieldsStr+' from '+@TableName;
IF(@WhereStr !=NULL OR @WhereStr!='')
Begin
SET    @TotalCount=@TotalCountStr+' where '+@WhereStr;
SET    @SqlStr=@SqlStr+' where '+@WhereStr;
END
EXEC sys.sp_executesql @TotalCountStr,N'@TotalCount int out',@TotalCount OUTPUT
SET    @SqlStr='select * from '+@SqlStr+')as temptable where rowId between '+LTRIM(STR(@StartNum))+' and '+LTRIM(STR(@EndNum));
EXEC(@SqlStr)
END




原创粉丝点击