大数据量分页存储过程
一:拼接字符串较长的
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 DBGO/****** Object: StoredProcedure [StoreHouse].[GetReturnGoodsInfo] Script Date: 04/03/2013 12:44:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate procedure [StoreHouse].[GetReturnGoodsInfo](@pageIndex int, --页索引@pageSize int, --页记录数@strsql varchar(4000), --查询语句@totalcount int output--总行数)asbegindeclare @sql nvarchar(4000)set @sql='select @totalcount=count(1) from ('+@strsql+') as tbaselect * from(select Row_number() over(order by ID asc) as IDRank,* from ('+@strsql+') as tba) as IDWithRowNumberwhere IDRank>'+str(@pageSize*(@pageIndex-1))+' and IDRank<='+str(@pageSize*@pageIndex)+''execute sp_executesql @sql,N'@totalcount int output',@totalcount outputend