大数据量分页存储过程

 

一：拼接字符串较长的

USE DB

GO

/****** Object:  StoredProcedure [StoreHouse].[GetReturnGoodsInfo2]    Script Date: 04/12/2013 16:34:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [StoreHouse].[GetReturnGoodsInfo2] 

( 

@cols VARCHAR(8000), 

@col_id VARCHAR(400), 

@sqlon VARCHAR(8000), 

@sqlwhere VARCHAR(8000), 

@col_orderby VARCHAR(400), 

@pageindex INT, 

@rownum INT, 

@totalnum INT OUTPUT 

) 

AS 

BEGIN 

SET NOCOUNT ON; 

DECLARE @sql VARCHAR(8000),@nsql NVARCHAR(4000),@tmptb VARCHAR(8000) 

SET @tmptb='[##t'+CONVERT(VARCHAR(8000),NEWID())+']' 

SET @sql='CREATE TABLE '+@tmptb+'(id_returngoodsinfo INT IDENTITY(1,1) PRIMARY KEY ,tid_returngoodsinfo INT)' 

EXEC(@sql) 

SET @sql=' 

INSERT INTO '+@tmptb+'(tid_returngoodsinfo) 

select '+@col_id+' from 

(select '+@cols+' from '+@sqlon+' 

'+@sqlwhere+' 

 

) as tbl 

'

print @sql 

EXEC(@sql) 

SET @nsql='SELECT @totalnum=COUNT(1) FROM '+@tmptb+'' 

EXEC sp_executesql @nsql ,N'@totalnum int output',@totalnum OUTPUT 

SET @sql=' 

select tbla.id_returngoodsinfo,tblb.* from '+@tmptb+' tbla join 

(select '+@cols+' from '+@sqlon+' )tblb 

on tbla.tid_returngoodsinfo=tblb.'+@col_id+' 

and tbla.id_returngoodsinfo between '+CONVERT(VARCHAR,((@pageindex-1)*@rownum+1))+'and '+ 

CONVERT(VARCHAR,(@pageindex*@rownum))+' 

' 

print @sql

EXEC(@sql) 

 

END 

二：拼接字符串较短的

USE DB

GO

/****** Object:  StoredProcedure [StoreHouse].[GetReturnGoodsInfo]    Script Date: 04/03/2013 12:44:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create procedure [StoreHouse].[GetReturnGoodsInfo]   

 ( 

     @pageIndex int,  --页索引 

     @pageSize int,    --页记录数 

     @strsql varchar(4000), --查询语句

     @totalcount int output--总行数

 ) 

 as 

 begin 

     declare @sql nvarchar(4000)

     set @sql='

     select @totalcount=count(1) from ('+@strsql+') as tba

     select * from 

     (

     select Row_number() over(order by ID asc) as IDRank,* from ('+@strsql+') as tba

     ) as IDWithRowNumber

     where IDRank>'+str(@pageSize*(@pageIndex-1))+' and IDRank<='+str(@pageSize*@pageIndex)+''

     execute sp_executesql @sql,N'@totalcount int output',@totalcount output

 end