SQL存储过程调用问题

2010-06-04 01:46:12 810 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

[调参神器]使用VOFA+上位机进行PID调参(附下位机代码) 简单地来说，VOFA+是一个超级串口助手，除了可以实现一般串口助手的串口数据收发，它还可以实现数据绘图（包括直方图、FFT图），控件编辑，图像显示等功能。如果只是想要用VOFA+来进行数据绘图，直接使用一个波形图控件就行，但是如果想要把VOFA+当作一个长期使用的调参助手，我们最好设置一下控件。VOFA+简洁好用，但是又有点太简洁了，部分体验并不是很好（比如不能调整控件大小，保存文件不太方便等），所以如果学有余力，推荐自己用QT/PyQT写上位机。接下来，我们要将指令中的数据提取出来。阅读详情

我有一个存储过程repascn1_qc_rep03_2单独执行能正常
如执行 exec repascn1_qc_rep03_2 '2010-4-17','2010-4-17' 能正常
执行repascn1_qc_rep03_2返回表的字段同以下#tt表一样

我在另一个存储过程repascn1_qc_rep26_2中写如下语句
@SDate,@EDate是repascn1_qc_rep26_2的参数,
create table #tt
(
WorkOrderNo varchar(20),
CustStyle varchar(20),
DepartmentId varchar(20),
QCQty int,
TotalPoint int,
Percents float,
Point1 int, Point2 int, Point3 int, Point4 int, Point5 int, Point6 int, Point7 int, Point8 int, Point9 int,
Point10 int, Point11 int, Point12 int, Point13 int, Point14 int, Point15 int, Point16 int,
Point17 int, Point18 int, Point19 int, Point20 int, Point21 int, Point22 int, Point23 int,
Point24 int, Point25 int, Point26 int, Point27 int, Point28 int, Point29 int, Point30 int,
Point31 int, Point32 int, Point33 int, Point34 int, Point35 int, Point36 int, Point37 int,
Remark varchar(2000),
Remark1 varchar(2000),
Remark2 varchar(2000)
)
insert into #tt exec repascn1_qc_rep03_2 @SDate,@EDate

我在执行repascn1_qc_rep26_2时在"insert into #tt exec repascn1_qc_rep03_2 @SDate,@EDate"一句报错"Invalid column name 'InputDate'."
InputDate是repascn1_qc_rep03_2中用到的字段，与repascn1_qc_rep26_2没有关系。

只有当运行了repascn1_qc_rep03_2的创建语句，而没运行repascn1_qc_rep03_2的执行语句，
直接执行repascn1_qc_rep26_2才报以上错，
如果运行repascn1_qc_rep03_2的创建语句后，有至少一次运行repascn1_qc_rep03_2的执行语句
再执行repascn1_qc_rep26_2是可以正常显示的。

请高人指点这是什么问题？

...全文

鼎阳示波器SCPI指令与MD5算法解锁带宽升级实战本文详细介绍了鼎阳示波器通过SCPI指令与MD5算法实现带宽升级的实战流程。从设备连接、信息获取到Python密钥生成脚本解析，逐步指导用户完成升级操作，并涵盖常见问题排查与功能选件激活指南，帮助用户充分释放设备潜能。阅读详情

nightmaple

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

nightmaple 2010-06-04

打赏
举报

[Quote=引用 10 楼 gaofeng723 的回复:]
检查两个存储过程中的临时表是否重名，重名时会出现问题.
[/Quote]

恩，是同名的原因，谢谢啦！

htl258_Tony 2010-06-04

打赏
举报

大致看了一下，把INTO 临时表改为创建临时表后再 EXEC('insert into ....')试试，因为你上面提示的是"无效的字段名InputDate",过程太长，还是你自己有环境调试一下吧

gaofeng723 2010-06-04

打赏
举报

检查两个存储过程中的临时表是否重名，重名时会出现问题.

nightmaple 2010-06-04

打赏
举报

接上



select a.WorkOrderNo,a.DeptId,a.PointDate,0 as QCQty,1 as TotalPoint,

case when DefCode=1 then 1 else 0 end as Point1,

case when DefCode=2 then 1 else 0 end as Point2,

case when DefCode=3 then 1 else 0 end as Point3,

case when DefCode=4 then 1 else 0 end as Point4,

case when DefCode=5 then 1 else 0 end as Point5,

case when DefCode=6 then 1 else 0 end as Point6,

case when DefCode=7 then 1 else 0 end as Point7,

case when DefCode=8 then 1 else 0 end as Point8,

case when DefCode=9 then 1 else 0 end as Point9,

case when DefCode=10 then 1 else 0 end as Point10,

case when DefCode=11 then 1 else 0 end as Point11,

case when DefCode=12 then 1 else 0 end as Point12,

case when DefCode=13 then 1 else 0 end as Point13,

case when DefCode=14 then 1 else 0 end as Point14,

case when DefCode=15 then 1 else 0 end as Point15,

case when DefCode=16 then 1 else 0 end as Point16,

case when DefCode=17 then 1 else 0 end as Point17,

case when DefCode=18 then 1 else 0 end as Point18,

case when DefCode=19 then 1 else 0 end as Point19,

case when DefCode=20 then 1 else 0 end as Point20,

case when DefCode=21 then 1 else 0 end as Point21,

case when DefCode=22 then 1 else 0 end as Point22,

case when DefCode=23 then 1 else 0 end as Point23,

case when DefCode=24 then 1 else 0 end as Point24,

case when DefCode=25 then 1 else 0 end as Point25,

case when DefCode=26 then 1 else 0 end as Point26,

case when DefCode=27 then 1 else 0 end as Point27,

case when DefCode=28 then 1 else 0 end as Point28,

case when DefCode=29 then 1 else 0 end as Point29,

case when DefCode=30 then 1 else 0 end as Point30,

case when DefCode=31 then 1 else 0 end as Point31,

case when DefCode=32 then 1 else 0 end as Point32,

case when DefCode=33 then 1 else 0 end as Point33,

case when DefCode=34 then 1 else 0 end as Point34,

case when DefCode=35 then 1 else 0 end as Point35,

case when DefCode=36 then 1 else 0 end as Point36,

case when DefCode=37 then 1 else 0 end as Point37

into #tempnew from MaFinalQCNewInputDet a

left join MaDepartment c (nolock) on a.DeptId=c.DepartmentId

left join TxWorkOrdStepHd d (nolock) on a.WorkOrderNo=d.WorkOrderNo

where a.WorkOrderNo between @SWorkOrder and @EWorkOrder

and a.PointDate between @SDate and @EDate

and a.DeptId between @SGroup and @EGroup

and d.CustCode between @SCust and @ECust

and d.CustCode in (select Field1 from MaGlobalDet where KeyNo=(select KeyNo from MaGlobalSetup where KeyName='QC40疵点客户清单'))

order by a.WorkOrderNo,a.DeptId



insert into #tempnew 

select a.WorkOrderNo,a.DeptId,left(a.ReturnDate,11),1,0,

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

from MaFinalQCNewInputDet a

left join MaDepartment c (nolock) on a.DeptId=c.DepartmentId

left join TxWorkOrdStepHd d (nolock) on a.WorkOrderNo=d.WorkOrderNo

where a.WorkOrderNo between @SWorkOrder and @EWorkOrder

and convert(datetime,left(a.ReturnDate,11)) between @SDate and @EDate

and a.DeptId between @SGroup and @EGroup

and d.CustCode between @SCust and @ECust

and d.CustCode in (select Field1 from MaGlobalDet where KeyNo=(select KeyNo from MaGlobalSetup where KeyName='QC40疵点客户清单'))

order by a.WorkOrderNo,a.DeptId



--select * from #tempnew return



insert into #result3 

select WorkOrderNo,DeptId,PointDate,sum(QCQty) as QCQty,sum(TotalPoint) as TotalPoint

,sum(Point1) as Point1,sum(Point2) as Point2,sum(Point3) as Point3,sum(Point4) as Point4

,sum(Point5) as Point5,sum(Point6) as Point6,sum(Point7) as Point7,sum(Point8) as Point8

,sum(Point9) as Point9,sum(Point10) as Point10,sum(Point11) as Point11,sum(Point12) as Point12

,sum(Point13) as Point13,sum(Point14) as Point14,sum(Point15) as Point15,sum(Point16) as Point16

,sum(Point17) as Point17,sum(Point18) as Point18,sum(Point19) as Point19,sum(Point20) as Point20

,sum(Point21) as Point21,sum(Point22) as Point22,sum(Point23) as Point23,sum(Point24) as Point24

,sum(Point25) as Point25,sum(Point26) as Point26,sum(Point27) as Point27,sum(Point28) as Point28

,sum(Point29) as Point29,sum(Point30) as Point30,sum(Point31) as Point31,sum(Point32) as Point32

,sum(Point33) as Point33,sum(Point34) as Point34,sum(Point35) as Point35,sum(Point36) as Point36

,sum(Point37) as Point37

from #tempnew

group by WorkOrderNo,DeptId,PointDate

----------------------------------------



--------------------------------------------------

declare @tempstr varchar(2000),@KeyName varchar(40),@Count int

SET @tempstr=''

if @Point35='Y' set @KeyName='QC大查40疵点2(报表)'

else if @Point37='Y' set @KeyName='QC大查40疵点(报表)'

else set @KeyName=''



select KeyNo,KeyId,right('000'+convert(varchar(4),Field1),2) as Field1,Field2

into #tmp from MaGlobalDet (nolock)

where KeyNo=(select KeyNo from MaGlobalSetup where KeyName=@KeyName) order by Seq



select @Count=count(*) from #tmp

while @Count<37

begin

	set @Count=@Count+1

	insert into #tmp values('','',right('000'+convert(varchar(4),@Count),2),'')	

end



SELECT @tempstr=@tempstr+Isnull(Field1,'')+':'+Isnull(Field2,'')+';' from #tmp Order by Field1

drop table #tmp

----------------------------------------------------



create table #tt

(

	WorkOrderNo varchar(20),

	CustStyle varchar(20),

	DepartmentId varchar(20),

	QCQty int,

	TotalPoint int,

	Percents float,

	Point1 int,	Point2 int,	Point3 int,	Point4 int,	Point5 int,	Point6 int,	Point7 int,	Point8 int,	Point9 int,

	Point10 int,	Point11 int,	Point12 int,	Point13 int,	Point14 int,	Point15 int,	Point16 int,

	Point17 int,	Point18 int,	Point19 int,	Point20 int,	Point21 int,	Point22 int,	Point23 int,

	Point24 int,	Point25 int,	Point26 int,	Point27 int,	Point28 int,	Point29 int,	Point30 int,

	Point31 int,	Point32 int,	Point33 int,	Point34 int,	Point35 int,	Point36 int,	Point37 int,

	Remark varchar(2000),

	Remark1 varchar(2000),

	Remark2 varchar(2000)

)

----------------------------------

if @Action in ('1','2')

begin

	select Order_No,CustStyle into #OrderCustStyle from GarmentServerLive.[DtradeSimpleGarment01].[dbo].[TX_Order_Detail] a



	insert into #tt

	select WorkOrderNo		--工作单编号

		,CustStyle

		,DepartmentId		--(组别)部门编号

		,sum(QCQty) as QCQty	--QC数量

		,sum(TotalPoint) as TotalPoint	--疵点总数量

		,case when isnull(sum(QCQty),0)=0 then 0 else convert(numeric(10,2),isnull(sum(TotalPoint),0))/sum(QCQty)*100 end as Percents

		,sum(Point1) as Point1,sum(Point2) as Point2,sum(Point3) as Point3,sum(Point4) as Point4

		,sum(Point5) as Point5,sum(Point6) as Point6,sum(Point7) as Point7,sum(Point8) as Point8

		,sum(Point9) as Point9,sum(Point10) as Point10,sum(Point11) as Point11,sum(Point12) as Point12

		,sum(Point13) as Point13,sum(Point14) as Point14,sum(Point15) as Point15,sum(Point16) as Point16

		,sum(Point17) as Point17,sum(Point18) as Point18,sum(Point19) as Point19,sum(Point20) as Point20

		,sum(Point21) as Point21,sum(Point22) as Point22,sum(Point23) as Point23,sum(Point24) as Point24

		,sum(Point25) as Point25,sum(Point26) as Point26,sum(Point27) as Point27,sum(Point28) as Point28

		,sum(Point29) as Point29,sum(Point30) as Point30,sum(Point31) as Point31,sum(Point32) as Point32

		,sum(Point33) as Point33,sum(Point34) as Point34,sum(Point35) as Point35,sum(Point36) as Point36

		,sum(Point37) as Point37,

		--,sum(Point38) as Point38,sum(Point39) as Point39,sum(Point40) as Point40

		Remark=replace(substring(@tempstr,0,charindex('15',@tempstr)),char(13),''),

		Remark1=replace(substring(@tempstr,charindex('15',@tempstr),charindex('28',@tempstr)-charindex('15',@tempstr)),char(13),''),  --报表最长支持152

		Remark2=replace(substring(@tempstr,charindex('28',@tempstr),len(@tempstr)),char(13),'')

	from #result3 a (nolock)

	left join #OrderCustStyle b on left(a.WorkOrderNo,10)=b.Order_No

	group by WorkOrderNo,CustStyle,DepartmentId

	having case when isnull(sum(QCQty),0)=0 then 0 else convert(numeric(10,2),isnull(sum(TotalPoint),0))/sum(QCQty)*100 end>=@Percent 

	order by DepartmentId,WorkOrderNo

end

-------------------------------------

if @Action in ('3','4')

insert into #tt

select '','',DepartmentId		--(组别)部门编号

	,sum(QCQty) as QCQty	--QC数量

	,sum(TotalPoint) as TotalPoint	--疵点总数量

	,case when isnull(sum(QCQty),0)=0 then 0 else convert(numeric(10,2),isnull(sum(TotalPoint),0))/sum(QCQty)*100 end as Percents

	,sum(Point1) as Point1,sum(Point2) as Point2,sum(Point3) as Point3,sum(Point4) as Point4

	,sum(Point5) as Point5,sum(Point6) as Point6,sum(Point7) as Point7,sum(Point8) as Point8

	,sum(Point9) as Point9,sum(Point10) as Point10,sum(Point11) as Point11,sum(Point12) as Point12

	,sum(Point13) as Point13,sum(Point14) as Point14,sum(Point15) as Point15,sum(Point16) as Point16

	,sum(Point17) as Point17,sum(Point18) as Point18,sum(Point19) as Point19,sum(Point20) as Point20

	,sum(Point21) as Point21,sum(Point22) as Point22,sum(Point23) as Point23,sum(Point24) as Point24

	,sum(Point25) as Point25,sum(Point26) as Point26,sum(Point27) as Point27,sum(Point28) as Point28

	,sum(Point29) as Point29,sum(Point30) as Point30,sum(Point31) as Point31,sum(Point32) as Point32

	,sum(Point33) as Point33,sum(Point34) as Point34,sum(Point35) as Point35,sum(Point36) as Point36

	,sum(Point37) as Point37,

--,sum(Point38) as Point38,sum(Point39) as Point39,sum(Point40) as Point40

	Remark=replace(substring(@tempstr,0,charindex('15',@tempstr)),char(13),''),

	Remark1=replace(substring(@tempstr,charindex('15',@tempstr),charindex('28',@tempstr)-charindex('15',@tempstr)),char(13),''),  --报表最长支持152

	Remark2=replace(substring(@tempstr,charindex('28',@tempstr),len(@tempstr)),char(13),'')

from #result3

--MaDepartment a (nolock) 

--left join #result3 b (nolock) on a.DepartmentId=b.DepartmentId

where DepartmentId between @SGroup and @EGroup

group by DepartmentId

having case when isnull(sum(QCQty),0)=0 then 0 else convert(numeric(10,2),isnull(sum(TotalPoint),0))/sum(QCQty)*100 end>=@Percent 

order by DepartmentId



select * from #tt

return

GO



exec repascn1_qc_rep03_2 '3','2010-4-17','2010-4-17','!','~','!','~','!','~','','Y',''

nightmaple 2010-06-04

打赏
举报

代码有点长
以下上被调代码



IF OBJECT_ID('repascn1_qc_rep03_2', 'P') IS NOT NULL 

    Drop PROCEDURE repascn1_qc_rep03_2

GO

create proc repascn1_qc_rep03_2(

@Action varchar(10),		--为1大查验货报告表,为3各组大查验货统计表,为4大查验货分析表

							--直接在reportinput中设置,存储过程中没有用

@SDate varchar(20),		--入单开始日期

@EDate varchar(20),		--入单结束日期

@SGroup varchar(20),		--工作单所在组别开始编号

@EGroup varchar(20),		--工作单所在组别结束编号

@SCust varchar(20),		--开始客户编号

@ECust varchar(20),		--结束客户编号

@SWorkOrder varchar(20),		--工作单开始编号

@EWorkOrder varchar(20),		--工作单结束编号

@Percent varchar(20),--百分比   --为N 按WorkOrderNo,DepartmentId分类统计

@Point35 varchar(4),

@Point37 varchar(4)

)

as 

set nocount on

if @SDate='!' or @SDate is null or isdate(@SDate)=0 SET @SDate='1800-01-01'

if @EDate='~' or @EDate is null or isdate(@EDate)=0 SET @EDate='3000-01-01'

if @SGroup='!' or @SGroup='' or @SGroup is null SET @SGroup='AC01'

if @EGroup='~' or @EGroup='' or @EGroup is null SET @EGroup='AC49'

if @SWorkOrder='!' or @SWorkOrder='' or @SWorkOrder is null SET @SWorkOrder=char(0)

if @EWorkOrder='~' or @EWorkOrder='' or @EWorkOrder is null SET @EWorkOrder=char(255)

if @SCust='!' or @SCust='' or @SCust is null SET @SCust=char(0)

if @ECust='~' or @ECust='' or @ECust is null SET @ECust=char(255)

if @Percent='!' or @Percent='' or @Percent is null SET @Percent='0'



--扫扉

select WorkOrderNo,DepartmentId,TicketDate,Quantity as Qty 

into #tmp1 from TxDeptTicketDet (nolock)

where TicketDate between @SDate and @EDate

and DepartmentId between @SGroup and @EGroup

--补扉

insert into #tmp1 select WorkOrderNo,DepartmentId,InputDate,QCQty 

from MaFinalQCManualInput (nolock)

where InputDate between @SDate and @EDate

and DepartmentId between @SGroup and @EGroup

--退回

insert into #tmp1 select WorkOrderNo,DeptId,InputDate,DefQty 

from MaQtyInputNew (nolock)

where InputDate between @SDate and @EDate

and DeptId between @SGroup and @EGroup



--#temp存放每一天每一张工作单的扫扉、补扉、退回数量总合(即检验数)

select a.WorkOrderNo,a.DepartmentId,a.TicketDate,sum(isnull(a.Qty,0)) as Qty

into #temp from #tmp1 a

left join TxWorkOrdStepHd b (nolock) on a.WorkOrderNo=b.WorkOrderNo

where b.CustCode between @SCust and @ECust

and a.WorkOrderNo between @SWorkOrder and @EWorkOrder

and b.CustCode in (select Field1 from MaGlobalDet where KeyNo=(select KeyNo from MaGlobalSetup where KeyName='QC40疵点客户清单'))

group by a.WorkOrderNo,a.DepartmentId,a.TicketDate

order by a.WorkOrderNo

drop table #tmp1



--#result存放所有满足条件的工作单疵点数据(TotalPoint即翻修数)

select a.WorkOrderNo	    	--工作单编号

	,DepartmentId=isnull(a.DeptId,'')		--(组别)部门编号

	,a.InputDate--日期

	,TotalPoint=

	case when @Point35='Y' then	

		isnull(a.Point1,0)+isnull(a.Point2,0)+isnull(a.Point3,0)+isnull(a.Point4,0)

		+isnull(a.Point5,0)+isnull(a.Point6,0)+isnull(a.Point7,0)+isnull(a.Point8,0)+isnull(a.Point9,0)

		+isnull(a.Point10,0)+isnull(a.Point11,0)+isnull(a.Point12,0)+isnull(a.Point13,0)+isnull(a.Point14,0)

		+isnull(a.Point15,0)+isnull(a.Point16,0)+isnull(a.Point17,0)+isnull(a.Point18,0)+isnull(a.Point19,0)

		+isnull(a.Point20,0)+isnull(a.Point21,0)+isnull(a.Point22,0)+isnull(a.Point23,0)+isnull(a.Point24,0)

		+isnull(a.Point25,0)+isnull(a.Point26,0)+isnull(a.Point27,0)+isnull(a.Point28,0)+isnull(a.Point29,0)

		+isnull(a.Point30,0)+isnull(a.Point31,0)+isnull(a.Point32,0)+isnull(a.Point33,0)+isnull(a.Point34,0)

		+isnull(a.Point35,0)

	else

		isnull(a.Point1,0)+isnull(a.Point2,0)+isnull(a.Point3,0)+isnull(a.Point4,0)

		+isnull(a.Point5,0)+isnull(a.Point6,0)+isnull(a.Point7,0)+isnull(a.Point8,0)+isnull(a.Point9,0)

		+isnull(a.Point10,0)+isnull(a.Point11,0)+isnull(a.Point12,0)+isnull(a.Point13,0)+isnull(a.Point14,0)

		+isnull(a.Point15,0)+isnull(a.Point16,0)+isnull(a.Point17,0)+isnull(a.Point18,0)+isnull(a.Point19,0)

		+isnull(a.Point20,0)+isnull(a.Point21,0)+isnull(a.Point22,0)+isnull(a.Point23,0)+isnull(a.Point24,0)

		+isnull(a.Point25,0)+isnull(a.Point26,0)+isnull(a.Point27,0)+isnull(a.Point28,0)+isnull(a.Point29,0)

		+isnull(a.Point30,0)+isnull(a.Point31,0)+isnull(a.Point32,0)+isnull(a.Point33,0)+isnull(a.Point34,0)

		+isnull(a.Point35,0)+isnull(a.Point36,0)+isnull(a.Point37,0)

	end

--+isnull(a.Point38,0)+isnull(a.Point39,0)+isnull(a.Point40,0)     --疵点总数量

	,isnull(a.Point1,0) as Point1,isnull(a.Point2,0) as Point2,isnull(a.Point3,0) as Point3

	,isnull(a.Point4,0) as Point4,isnull(a.Point5,0) as Point5,isnull(a.Point6,0) as Point6

	,isnull(a.Point7,0) as Point7,isnull(a.Point8,0) as Point8,isnull(a.Point9,0) as Point9

	,isnull(a.Point10,0) as Point10,isnull(a.Point11,0) as Point11,isnull(a.Point12,0) as Point12

	,isnull(a.Point13,0) as Point13,isnull(a.Point14,0) as Point14,isnull(a.Point15,0) as Point15

	,isnull(a.Point16,0) as Point16,isnull(a.Point17,0) as Point17,isnull(a.Point18,0) as Point18

	,isnull(a.Point19,0) as Point19,isnull(a.Point20,0) as Point20,isnull(a.Point21,0) as Point21

	,isnull(a.Point22,0) as Point22,isnull(a.Point23,0) as Point23,isnull(a.Point24,0) as Point24

	,isnull(a.Point25,0) as Point25,isnull(a.Point26,0) as Point26,isnull(a.Point27,0) as Point27

	,isnull(a.Point28,0) as Point28,isnull(a.Point29,0) as Point29,isnull(a.Point30,0) as Point30

	,isnull(a.Point31,0) as Point31,isnull(a.Point32,0) as Point32,isnull(a.Point33,0) as Point33

	,isnull(a.Point34,0) as Point34,isnull(a.Point35,0) as Point35,isnull(a.Point36,0) as Point36

	,isnull(a.Point37,0) as Point37

--,isnull(a.Point38,0) as Point38,isnull(a.Point39,0) as Point39,isnull(a.Point40,0) as Point40

	,DepartmentName=isnull(c.Description,'')--上大组

	,d.CustCode	--客户代码

	,d.StyleNo--款号

into #result

from MaQtyInputNew a (nolock)

left join MaDepartment c (nolock) on a.DeptId=c.DepartmentId

left join TxWorkOrdStepHd d (nolock) on a.WorkOrderNo=d.WorkOrderNo

where a.InputDate between @SDate and @EDate

and a.DeptId between @SGroup and @EGroup

and d.CustCode between @SCust and @ECust

and a.WorkOrderNo between @SWorkOrder and @EWorkOrder

and d.CustCode in (select Field1 from MaGlobalDet where KeyNo=(select KeyNo from MaGlobalSetup where KeyName='QC40疵点客户清单'))

order by a.WorkOrderNo,a.DeptId



--将#result中数据按WorkOrderNo,DepartmentId,InputDate汇总

select WorkOrderNo,DepartmentId,InputDate

	,sum(TotalPoint) as TotalPoint

	,sum(Point1) as Point1,sum(Point2) as Point2,sum(Point3) as Point3,sum(Point4) as Point4

	,sum(Point5) as Point5,sum(Point6) as Point6,sum(Point7) as Point7,sum(Point8) as Point8

	,sum(Point9) as Point9,sum(Point10) as Point10,sum(Point11) as Point11,sum(Point12) as Point12

	,sum(Point13) as Point13,sum(Point14) as Point14,sum(Point15) as Point15,sum(Point16) as Point16

	,sum(Point17) as Point17,sum(Point18) as Point18,sum(Point19) as Point19,sum(Point20) as Point20

	,sum(Point21) as Point21,sum(Point22) as Point22,sum(Point23) as Point23,sum(Point24) as Point24

	,sum(Point25) as Point25,sum(Point26) as Point26,sum(Point27) as Point27,sum(Point28) as Point28

	,sum(Point29) as Point29,sum(Point30) as Point30,sum(Point31) as Point31,sum(Point32) as Point32

	,sum(Point33) as Point33,sum(Point34) as Point34,sum(Point35) as Point35,sum(Point36) as Point36

	,sum(Point37) as Point37

--,sum(Point38) as Point38,sum(Point39) as Point39,sum(Point40) as Point40

into #result2 from #result (nolock)

group by WorkOrderNo,DepartmentId,InputDate

drop table #result



--#temp4中存放检验数，#result2中存放翻修数

select a.WorkOrderNo,a.DepartmentId,b.InputDate,

a.Qty as QCQty,isnull(TotalPoint,0) as TotalPoint,

isnull(Point1,0) as Point1,isnull(Point2,0) as Point2,

isnull(Point3,0) as Point3,isnull(Point4,0) as Point4,

isnull(Point5,0) as Point5,isnull(Point6,0) as Point6,

isnull(Point7,0) as Point7,isnull(Point8,0) as Point8,

isnull(Point9,0) as Point9,isnull(Point10,0) as Point10,

isnull(Point11,0) as Point11,isnull(Point12,0) as Point12,

isnull(Point13,0) as Point13,isnull(Point14,0) as Point14,

isnull(Point15,0) as Point15,isnull(Point16,0) as Point16,

isnull(Point17,0) as Point17,isnull(Point18,0) as Point18,

isnull(Point19,0) as Point19,isnull(Point20,0) as Point20,

isnull(Point21,0) as Point21,isnull(Point22,0) as Point22,

isnull(Point23,0) as Point23,isnull(Point24,0) as Point24,

isnull(Point25,0) as Point25,isnull(Point26,0) as Point26,

isnull(Point27,0) as Point27,isnull(Point28,0) as Point28,

isnull(Point29,0) as Point29,isnull(Point30,0) as Point30,

isnull(Point31,0) as Point31,isnull(Point32,0) as Point32,

isnull(Point33,0) as Point33,isnull(Point34,0) as Point34,

isnull(Point35,0) as Point35,isnull(Point36,0) as Point36,

isnull(Point37,0) as Point37

--,isnull(Point38,0) as Point38,isnull(Point39,0) as Point39,isnull(Point40,0) as Point40

into #result3 

from #temp a (nolock)

left join #result2 b (nolock) on a.WorkOrderNo=b.WorkOrderNo  --left 不知是否准确,不能用inner

and a.DepartmentId=b.DepartmentId and b.InputDate=a.TicketDate

drop table #temp

drop table #result2



--select * from #result3 return

一版贴不完

htl258_Tony 2010-06-04

打赏
举报

[Quote=引用 5 楼 nightmaple 的回复:]
我用的是其中的第二个方法，被调存储过程返回结果放临时表中，也没有循环调用或嵌套调用

我的问题是
如果只执行A的创建语句，不执行A的执行语句，当执行B时会报错
而执行A的创建语句后，也执行一下A的执行语句，再执行B就不会报错

一般什么情况下才会出现这种情况啊？
[/Quote]
一般是编译的问题，但被你描述的很复杂。

htl258_Tony 2010-06-04

打赏
举报

这个吧，把两个过程的代码都SHOW一下。

nightmaple 2010-06-04

打赏
举报

我用的是其中的第二个方法，被调存储过程返回结果放临时表中，也没有循环调用或嵌套调用

我的问题是
如果只执行A的创建语句，不执行A的执行语句，当执行B时会报错
而执行A的创建语句后，也执行一下A的执行语句，再执行B就不会报错

一般什么情况下才会出现这种情况啊？

--小F-- 2010-06-04

打赏
举报

第一种方法: 使用output参数



USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL 

    DROP PROCEDURE Production.usp_GetList;

GO

CREATE PROCEDURE Production.usp_GetList @product varchar(40) 

    , @maxprice money 

    , @compareprice money OUTPUT

    , @listprice money OUT

AS

    SELECT p.name AS Product, p.ListPrice AS 'List Price'

    FROM Production.Product p

    JOIN Production.ProductSubcategory s 

      ON p.ProductSubcategoryID = s.ProductSubcategoryID

    WHERE s.name LIKE @product AND p.ListPrice < @maxprice;

-- Populate the output variable @listprice.

SET @listprice = (SELECT MAX(p.ListPrice)

        FROM Production.Product p

        JOIN  Production.ProductSubcategory s 

          ON p.ProductSubcategoryID = s.ProductSubcategoryID

        WHERE s.name LIKE @product AND p.ListPrice < @maxprice);

-- Populate the output variable @compareprice.

SET @compareprice = @maxprice;

GO





另一个存储过程调用的时候:



Create Proc Test

as

DECLARE @compareprice money, @cost money 

EXECUTE Production.usp_GetList '%Bikes%', 700, 

    @compareprice OUT, 

    @cost OUTPUT

IF @cost <= @compareprice 

BEGIN

    PRINT 'These products can be purchased for less than 

    $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'

END

ELSE

    PRINT 'The prices for all products in this category exceed 

    $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

第二种方法:创建一个临时表



create proc GetUserName

as

begin

    select 'UserName'

end



Create table #tempTable (userName nvarchar(50))

insert into #tempTable(userName)

exec GetUserName



select #tempTable



--用完之后要把临时表清空

drop table #tempTable--需要注意的是，这种方法不能嵌套。例如：



  procedure   a   

  begin   

      ...   

      insert   #table   exec   b   

  end   

    

  procedure   b   

  begin   

      ...   

      insert   #table    exec   c   

      select   *   from   #table     

  end   

    

  procedure   c   

  begin   

      ...   

      select   *   from   sometable   

  end  



--这里ａ调ｂ的结果集，而ｂ中也有这样的应用ｂ调了ｃ的结果集，这是不允许的，

--会报“INSERT EXEC 语句不能嵌套”错误。在实际应用中要避免这类应用的发生。





第三种方法:声明一个变量,用exec(@sql)执行:



1);EXEC 执行SQL语句



declare @rsql varchar(250)

        declare @csql varchar(300)

        declare @rc nvarchar(500)

        declare @cstucount int

        declare @ccount int

        set @rsql='(select Classroom_id from EA_RoomTime where zc='+@zc+' and xq='+@xq+' and T'+@time+'=''否'') and ClassroomType=''1'''

        --exec(@rsql)

        set @csql='select @a=sum(teststucount),@b=sum(classcount) from EA_ClassRoom where classroom_id in '

        set @rc=@csql+@rsql

        exec sp_executesql @rc,N'@a int output,@b int output',@cstucount output,@ccount output--将exec的结果放入变量中的做法

        --select @csql+@rsql

        --select @cstucount







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/23/4584118.aspx

--小F-- 2010-06-04

打赏
举报

是不是存储过程调用存储过程??

nightmaple 2010-06-04

打赏
举报

简单的说就是：
在一个存储过程B中调另一个存储过程A
A单独执行正常

如果只执行A的创建语句，不执行A的执行语句，当执行B时会报错
而执行A的创建语句后，也执行一下A的执行语句，再执行B就不会报错

报错处就是B中调A的那个语句
报的错就是A中的一个字段没定义

就是这样了，请再看看，我真的弄不清是怎么回事。

playwarcraft 2010-06-04

打赏
举报

饶的我头晕。。。

SQL存储过程调用问题

相关推荐