将它的性能尽量优化(优化后不改变原来的调用方法,不影响原来调用该存储过程的程序的正确运行)。
/****** 对象: StoredProcedure [dbo].[tableForPage] 脚本日期: 03/30/2009 19:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[tableForPage]@pagenow int ,
@pagesize int ,
@idname varchar(50),
@sorttye int,
@SelectClause varchar(4000),
@sqlWhere varchar(4000),-- only where clause,no order clause added by
@table varchar(500),
@intRecordCount int outputas
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intPageCount int
declare @intRowCount int
declare @sql nvarchar(4000)--定义sql语句/*关闭计数*/
set nocount on/*求总数*/
set @sql='select @a=count(*) from '+@table+' '+@sqlWhere
exec sp_executesql @sql,N'@a int output',@intRecordCount output
/*判断页数是否正确*/
---do nothing
/*求开始ID*/
set @intRowCount = (@pagenow - 1) * @pagesize + 1
/*限制条数*/
set rowcount @intRowCount
if @sorttye=0
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' '
else
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' desc 'exec sp_executesql @sql,N'@a int output',@intBeginID output/*结束ID*/
set @intRowCount = @pagenow * @pagesize
/*限制条数*/
set rowcount @intRowCount
if @sorttye=0
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' '
else
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' desc 'exec sp_executesql @sql,N'@a int output',@intEndID output/*恢复系统变量*/
set rowcount 0
set nocount off
set @sql=@SelectClause+' from '+@table +' '
set @sql=@sql+@sqlWhere
if @sorttye=0
set @sql=@sql+' and '+@idname+' between '+str(@intBeginID)+' and '+str(@intEndID)+' order by '+@idname+' '
else
set @sql=@sql+' and '+@idname+' between '+str(@intEndID)+' and '+str(@intBeginID)+' order by '+@idname+' desc '--select(@sql)
exec(@sql)
return(@@rowcount)
/****** 对象: StoredProcedure [dbo].[tableForPage] 脚本日期: 03/30/2009 19:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[tableForPage]@pagenow int ,
@pagesize int ,
@idname varchar(50),
@sorttye int,
@SelectClause varchar(4000),
@sqlWhere varchar(4000),-- only where clause,no order clause added by
@table varchar(500),
@intRecordCount int outputas
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intPageCount int
declare @intRowCount int
declare @sql nvarchar(4000)--定义sql语句/*关闭计数*/
set nocount on/*求总数*/
set @sql='select @a=count(*) from '+@table+' '+@sqlWhere
exec sp_executesql @sql,N'@a int output',@intRecordCount output
/*判断页数是否正确*/
---do nothing
/*求开始ID*/
set @intRowCount = (@pagenow - 1) * @pagesize + 1
/*限制条数*/
set rowcount @intRowCount
if @sorttye=0
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' '
else
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' desc 'exec sp_executesql @sql,N'@a int output',@intBeginID output/*结束ID*/
set @intRowCount = @pagenow * @pagesize
/*限制条数*/
set rowcount @intRowCount
if @sorttye=0
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' '
else
set @sql='select @a = '+@idname+' from '+@table+' '+@sqlWhere+' order by '+@idname+' desc 'exec sp_executesql @sql,N'@a int output',@intEndID output/*恢复系统变量*/
set rowcount 0
set nocount off
set @sql=@SelectClause+' from '+@table +' '
set @sql=@sql+@sqlWhere
if @sorttye=0
set @sql=@sql+' and '+@idname+' between '+str(@intBeginID)+' and '+str(@intEndID)+' order by '+@idname+' '
else
set @sql=@sql+' and '+@idname+' between '+str(@intEndID)+' and '+str(@intBeginID)+' order by '+@idname+' desc '--select(@sql)
exec(@sql)
return(@@rowcount)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货