62,046
社区成员
发帖
与我相关
我的任务
分享请帮我看我存储过程，小弟谢过了~
小弟不懂存储过程，麻烦各位大大看看-
USE [BMC158]
GO
/****** Object: StoredProcedure [dbo].[GetOrderParameterByGroup] Script Date: 03/28/2011 20:48:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetOrderParameterByGroup]
@tblName varchar(1000), -- 表名
@SelectFieldName varchar(4000), -- 要显示的字段名(不要加select)
@strWhere varchar(4000), -- 查询条件(注意: 不要加 where)
@OrderFieldName varchar(255), -- 排序索引字段名
@PageSize int , -- 页大小
@PageIndex int = 1, -- 页码
@iRowCount int output, -- 返回记录总数
@OrderType int = 0, -- 设置排序类型, 非 0 值则降序
@GroupByFieldName varchar(4000) --设置group by 的字段
AS
declare @strSQL varchar(4000) -- 主语句
declare @strTmp varchar(4000) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strRowCount nvarchar(4000) -- 用于查询记录总数的语句
declare @strTable nvarchar(4000) --汇总表的语句前部分
declare @tempwhere nvarchar(4000) --汇总语句的后面部分语句
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
set @strTable='select parentorder,childorder,userid,usercode,sum(convert(float,totalprice)) as totalprice,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor from [OrderParameter]'
set @tempwhere= ' group by parentorder,childorder,userid,usercode,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor'
set @strSQL = 'select top ' + str(@PageSize)+ ' ' + @SelectFieldName+' from '
+ @tblName + ' where ' + @strWhere + @GroupByFieldName + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName + ' group by ' + @GroupByFieldName + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top '+ str(@PageSize) + ' '+ @SelectFieldName+' from '
+ @tblName + ' where ' + @strWhere + @GroupByFieldName + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + @GroupByFieldName + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' group by ' + @GroupByFieldName + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectFieldName+' from '
+ @tblName + @strTmp + ' group by '+ @GroupByFieldName + @strOrder
end
exec(@strSQL)
print @strSQL
if @strWhere!=''
begin
set @strRowCount = 'select @iRowCount=count(*) from (' + @strTable +' where '+@strWhere + @tempwhere +') as temptable'
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from (' + @strTable + @tempwhere +') as temptable'
end
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out
...全文
请发表友善的回复…
发表回复
wosizy 2011-03-28
- 打赏
- 举报
你先调试下 vs2008 可以调试存储过程的
或者去看看
二分法 存储过程 有调用列子！
http://www.cnblogs.com/moozi/archive/2008/04/16/1157021.html
或者去看看
二分法 存储过程 有调用列子！
http://www.cnblogs.com/moozi/archive/2008/04/16/1157021.html
灰太狼已不再单身 2011-03-28
- 打赏
- 举报
我这个存储过程是 group by 的，聚合函数的，分类统计
USE [BMC158]
GO
/****** Object: StoredProcedure [dbo].[GetOrderParameterByGroup] Script Date: 03/28/2011 20:48:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetOrderParameterByGroup]
@tblName varchar(1000), -- 表名
@SelectFieldName varchar(4000), -- 要显示的字段名(不要加select)
@strWhere varchar(4000), -- 查询条件(注意: 不要加 where)
@OrderFieldName varchar(255), -- 排序索引字段名
@PageSize int , -- 页大小
@PageIndex int = 1, -- 页码
@iRowCount int output, -- 返回记录总数
@OrderType int = 0, -- 设置排序类型, 非 0 值则降序
@GroupByFieldName varchar(4000) --设置group by 的字段
AS
declare @strSQL varchar(4000) -- 主语句
declare @strTmp varchar(4000) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strRowCount nvarchar(4000) -- 用于查询记录总数的语句
declare @strTable nvarchar(4000) --汇总表的语句前部分
declare @tempwhere nvarchar(4000) --汇总语句的后面部分语句
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
set @strTable='select parentorder,childorder,userid,usercode,sum(convert(float,totalprice)) as totalprice,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor from [OrderParameter]'
set @tempwhere= ' group by parentorder,childorder,userid,usercode,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor'
set @strSQL = 'select top ' + str(@PageSize)+ ' ' + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ ' ' + @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != ''
set @strSQL = 'select top '+ str(@PageSize) + ' '+ @SelectFieldName+' from '
+ @tblName + ' where ' + ' ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' group by ' + @GroupByFieldName + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectFieldName+' from '
+ @tblName + @strTmp + ' ' + ' group by '+ @GroupByFieldName + ' ' + @strOrder
end
exec(@strSQL)
print @strSQL
if @strWhere!=''
begin
set @strRowCount = 'select @iRowCount=count(*) from (' + @strTable +' where '+@strWhere + @tempwhere +') as temptable'
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from (' + @strTable + @tempwhere +') as temptable'
end
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out
灰太狼已不再单身 2011-03-28
- 打赏
- 举报
调试出来了，大家赶紧接分
灰太狼已不再单身 2011-03-28
- 打赏
- 举报
我知道有现成的，我是改这个，出错误了 -
guoyanhong1111 2011-03-28
- 打赏
- 举报
这个网上应该有现成的啊！
灰太狼已不再单身 2011-03-28
- 打赏
- 举报
pageindex =2 时 ，出的这个错误：
消息 102，级别 15，状态 1，第 1 行
'parentorder' 附近有语法错误。
消息 102，级别 15，状态 1，第 1 行
'parentorder' 附近有语法错误。
select top 2 parentorder,childorder,userid,usercode,sum(convert(float,totalprice)) as totalprice,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor from [OrderParameter] where 1=1[parentorder],childorder,userid,usercode,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactorcreatetime<(select min(createtime) from (select top 2createtime from [OrderParameter] where 1=1[parentorder],childorder,userid,usercode,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor order by createtime desc) as tblTmp) and 1=1 group by [parentorder],childorder,userid,usercode,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor order by createtime desc
灰太狼已不再单身 2011-03-28
- 打赏
- 举报
这是我调试的
USE [BMC158]
GO
DECLARE @return_value int,
@iRowCount int
EXEC @return_value = [dbo].[GetOrderParameterByGroup]
@tblName = N'[OrderParameter]',
@SelectFieldName = N'parentorder,childorder,userid,usercode,sum(convert(float,totalprice)) as totalprice,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor',
@strWhere = N'1=1',
@OrderFieldName = N'createtime',
@PageSize = 2,
@PageIndex = 2,
@iRowCount = @iRowCount OUTPUT,
@OrderType = 1,
@GroupByFieldName = N'[parentorder],childorder,userid,usercode,companyid,companycode,poststyle,postno,username,telephone,cellphone,email,[address],postnumber,[state],createtime,examinetime,examineid,examinename,posttime,postuser,closetime,closeuser,closegoods,returngoods,paytime,isaging,agingterm,agingrate,agingtatal,termpay,agingid,[description],isfactor'
SELECT @iRowCount as N'@iRowCount'
SELECT 'Return Value' = @return_value
GO
子夜__ 2011-03-28
- 打赏
- 举报
分页存储过程。。怎么不会用吗？
参考
aspnetpager也可以使用分页存储过程
参考
aspnetpager也可以使用分页存储过程
灰太狼已不再单身 2011-03-28
- 打赏
- 举报
当 pageindex =1 没错， pageindex=2 ，就出错了
