SQL server 2008 分页存储过程

来源：互联网发布：卖家淘宝店名可以改吗编辑：程序博客网时间：2024/05/19 23:13

GO/****** Object:  StoredProcedure [dbo].[T_GetPager]    Script Date: 03/23/2015 14:35:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[T_GetPager]    @PageSize INT,    @PageIndex INT,    @orderby varchar(150),  @strWhere varchar(4000),  @tablename varchar(64),  @cols varchar(4000)  ,@groupby varchar(256)   AS     Begin   declare @strSQL varchar(5000)   declare @b bigint,@e bigint   declare @order varchar(160)      IF(@orderby<>'')    SET @order = ' ORDER BY '+@orderby   ELSE    SET @order = ' ORDER BY  id DESC'     SET @b=@PageSize*(@PageIndex-1)+1   SET @e=@PageSize*@PageIndex     SET @strSQL = 'select  ' + @cols   SET @strSQL = @strSQL + ' from '   SET @strSQL = @strSQL+ '(select row_number() over (' + @order + ') row ,' + @cols + ' from ' + @tablename   IF (@strWhere<>'')    SET @strSQL= @strSQL + ' WHERE ' + @strWhere      IF(@groupby<>'')SET @strSQL=@strSQL + ' group by ' + @groupby  SET @strSQL = @strSQL + ') '   SET @strSQL = @strSQL + @tablename    if(@PageSize>0)beginSET @strSQL = @strSQL + ' where row between ' + CAST(@b AS varchar)  SET @strSQL = @strSQL + ' and ' + CAST(@e AS varchar)  end       IF(@groupby<>'')SET @strSQL=@strSQL + ' group by ' + @groupby   SET @strSQL = @strSQL +' ' + @order  End    print @strSQLexec(@strSQL)

0 0