SQL存储过程调用问题

[nightmaple]

我有一个存储过程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是可以正常显示的。

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

[playwarcraft]

饶的我头晕。。。

[nightmaple]

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

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

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

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

[--小F--]

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

[--小F--]

第一种方法: 使用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

[nightmaple]

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

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

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

[htl258_Tony]

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

[htl258_Tony]

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

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

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

[nightmaple]

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

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

一版贴不完

[nightmaple]

接上

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',''

[gaofeng723]

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

[htl258_Tony]

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

[nightmaple]

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

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