对SQL不太懂,网上找了一个通用性近乎完美的存储过程分页,不知性能如何请大家帮我分析分析如下:CREATE PROCEDURE up_GetTopicList
@a_TableList Varchar(200),
@a_TableName Varchar(30),
@a_SelectWhere Varchar(500),
@a_SelectOrderId Varchar(20),
@a_SelectOrder Varchar(50),
@a_intPageNo int,
@a_intPageSize int,
@RecordCount int OUTPUT
as
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar(600)
/*关闭计数*/
set nocount on
/*求总共根贴数*/ select @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUTselect @RecordCount = @intRootRecordCount if (@intRootRecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1) /*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/*限制条数*/ select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT
/*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/*限制条数*/ select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintEndID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintEndID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT
if @a_SelectWhere='' or @a_SelectWhere IS NULL
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between 'if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder execute sp_executesql
@TmpSelect,
N'@SPintEndID int,@SPintBeginID int',
@SPintEndID=@intEndID,@SPintBeginID=@intBeginID return(@@rowcount)
--select @@rowcount
GO
解决方案 »
- 求一个关于多组资料同时上传时的思路
- 创建不规则表格
- treeview在页面首次加载时候无法遍历节点设置url的问题,很急,在线等!!!!!!!!!!!!!!
- 新手问题:很多控件都带更新,插入功能,但有问题的,自己写还是用这些控件的现成的功能呢?
- 怎样从aspx中直接打开客户端的xls的文件中的内容而不用上传这个xls文件
- (int)a和Convert.ToInt32(a)有什么区别?为什么有时候用第一个可以,第二个则不行?
- 在Web下如何放大缩小,平移JPG格式图片?
- 图片存入数据库问题!数据库是sql server!
- 高手进
- dotnet2.0 webrequest 获取https要登陆后的页面
- 如何在DataGrid模板列的RadioButtonList.Items(0).Text上绑定数据库中的值?
- 搜文件
另外还有一个生成比较简单的分页存储过程的工具
http://www.zgso.com/std/wpage.aspx