我有一个存储过程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,
Re varchar(2000),
Re1 varchar(2000),
Re2 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是可以正常显示的。请高人指点这是什么问题？
如执行 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,
Re varchar(2000),
Re1 varchar(2000),
Re2 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是可以正常显示的。请高人指点这是什么问题？
在一个存储过程B中调另一个存储过程A
A单独执行正常如果只执行A的创建语句，不执行A的执行语句，当执行B时会报错
而执行A的创建语句后，也执行一下A的执行语句，再执行B就不会报错报错处就是B中调A的那个语句
报的错就是A中的一个字段没定义就是这样了，请再看看，我真的弄不清是怎么回事。
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'
endCreate table #tempTable (userName nvarchar(50))
insert into #tempTable(userName)
exec GetUserNameselect #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
如果只执行A的创建语句，不执行A的执行语句，当执行B时会报错
而执行A的创建语句后，也执行一下A的执行语句，再执行B就不会报错一般什么情况下才会出现这种情况啊？
以下上被调代码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
一版贴不完
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.DeptIdinsert 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 returninsert 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 Seqselect @Count=count(*) from #tmp
while @Count<37
begin
set @Count=@Count+1
insert into #tmp values('','',right('000'+convert(varchar(4),@Count),2),'')
endSELECT @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,
Re varchar(2000),
Re1 varchar(2000),
Re2 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
Re=replace(substring(@tempstr,0,charindex('15',@tempstr)),char(13),''),
Re1=replace(substring(@tempstr,charindex('15',@tempstr),charindex('28',@tempstr)-charindex('15',@tempstr)),char(13),''), --报表最长支持152
Re2=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
Re=replace(substring(@tempstr,0,charindex('15',@tempstr)),char(13),''),
Re1=replace(substring(@tempstr,charindex('15',@tempstr),charindex('28',@tempstr)-charindex('15',@tempstr)),char(13),''), --报表最长支持152
Re2=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 DepartmentIdselect * from #tt
return
GOexec repascn1_qc_rep03_2 '3','2010-4-17','2010-4-17','!','~','!','~','!','~','','Y',''