環境:SQL SERVER 2008 R2 EXPRESS
疑惑:EXECUTE [dbo].[sp_visualSummary] 'FAB3' 執行這樣的語句不返回任何結果集。但是選擇“存儲過程---右擊執行存儲過程”會有結果返回,請教這是什麼問題造成的?
USE [NEWCPC]
GO
/****** Object: StoredProcedure [dbo].[sp_visualSummary] Script Date: 05/05/2016 11:43:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_visualSummary]
(
@stFactoryName nvarchar(10)
)
AS
BEGIN
DECLARE @stShiftNO int,
@currentHour int,
@currentMinute int
DECLARE @temp varchar(50),
@error int,
@product_type nvarchar(20),
@material_id nvarchar(30),
@plan_qty int
DECLARE @Summary_Report TABLE (
[Factory] nvarchar(10),
[ProductType] nvarchar(10),
[MaterialID] nvarchar(50),
[StandardTarget] nvarchar(10),
[OutputQty] int,
[Rate] float,
[1Count] nvarchar(50),
[2Count] nvarchar(50),
[3Count] nvarchar(50),
[4Count] nvarchar(50),
[5Count] nvarchar(50),
[ShiftNO] int
)
DECLARE @@1_count int,@@2_count int,@@3_count int,@@4_count int,@@5_count int
DECLARE @target_qty int,@output_qty int,@rate float,@2hours_planQty int
SET @@1_count=0
SET @@2_count=0
SET @@3_count=0
SET @@4_count=0
SET @@5_count=0
SET @target_qty=0
SET @output_qty=0
SET @rate=0
SET @2hours_planQty=0
BEGIN
SET @2hours_planQty=(SELECT [ParamValue] FROM [NEWCPC].[dbo].[ParameterSetting] WHERE [ParamName]='2HoursPlanQty')
END
SET @currentHour=DATENAME(HOUR,getdate())
SET @currentMinute=DATENAME(MINUTE,getdate())
SET @error=0
SET @plan_qty=0
IF (@currentHour >= 8 AND @currentHour <=20)
BEGIN
SET @stShiftNO = 1
END
ELSE
BEGIN
SET @stShiftNO = 2
END
BEGIN TRAN
DECLARE WO_CURSOR CURSOR FOR
SELECT
WO.ProductType,
WO.MaterialID,
SUM(WO.StandardTarget) AS PlanQty
FROM [NEWCPC].[dbo].[EquipmentInfo] EP INNER JOIN [NEWCPC].[dbo].[WorkOrderInfo] WO ON
EP.Factory=WO.Factory
AND EP.Factory=@stFactoryName
AND EP.EQPID=WO.EQPID
AND WO.WOStatus='Start'
GROUP BY WO.ProductType,WO.MaterialID
OPEN WO_CURSOR
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM WO_CURSOR INTO @product_type,@material_id,@plan_qty
BEGIN
IF @stShiftNO=1
BEGIN
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,8,10,@@1_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,10,12,@@2_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,13,15,@@3_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,15,17,@@4_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,18,20,@@5_count OUTPUT
SET @output_qty=@@1_count+@@2_count+@@3_count+@@4_count+@@5_count
SET @rate=@output_qty/@plan_qty
END
ELSE
BEGIN
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,20,22,@@1_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,22,24,@@2_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,1,3,@@3_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,3,5,@@4_count OUTPUT
execute dbo.sp_GetOutputQtyBy2Hours @stFactoryName,@material_id,6,8,@@5_count OUTPUT
SET @output_qty=@@1_count+@@2_count+@@3_count+@@4_count+@@5_count
SET @rate=(CONVERT(float,@output_qty)/CONVERT(float,@plan_qty))*100
END
--INSERT INTO @Summary_Report([Factory],[ProductType],[MaterialID],[StandardTarget],[OutputQty],[Rate],[1Count],[2Count],[3Count],[4Count],[5Count],[ShiftNO])
--VALUES(@stFactoryName,@product_type,@material_id,@plan_qty,@output_qty,@rate,CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@1_count)/CONVERT(float,@2hours_planQty)*100)+'%',
--CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@2_count)/CONVERT(float,@2hours_planQty)*100)+'%',
--CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@3_count)/CONVERT(float,@2hours_planQty)*100)+'%',
--CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@4_count)/CONVERT(float,@2hours_planQty)*100)+'%',
--CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@5_count)/CONVERT(float,@2hours_planQty)*100)+'%',
--@stShiftNO)
INSERT INTO @Summary_Report
SELECT @stFactoryName AS [Factory],
@product_type AS [ProductType],
@material_id AS [MaterialID],
@plan_qty AS [StandardTarget],
@output_qty AS [OutputQty],
@rate AS [Rate],
CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@1_count)/CONVERT(float,@2hours_planQty)*100)+'%' AS [1Count],
CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@2_count)/CONVERT(float,@2hours_planQty)*100)+'%' AS [2Count],
CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@3_count)/CONVERT(float,@2hours_planQty)*100)+'%' AS [3Count],
CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@4_count)/CONVERT(float,@2hours_planQty)*100)+'%' AS [4Count],
CONVERT(NVARCHAR,@2hours_planQty)+'/'+CONVERT(NVARCHAR,CONVERT(float,@@5_count)/CONVERT(float,@2hours_planQty)*100)+'%' AS [5Count],
@stShiftNO AS [ShiftNO]
COMMIT
FETCH NEXT FROM WO_CURSOR INTO @product_type,@material_id,@plan_qty
END
END
IF @error=0
BEGIN
commit tran
END
ELSE
BEGIN
rollback tran
END
CLOSE WO_CURSOR
DEALLOCATE WO_CURSOR
BEGIN
SELECT
*
FROM @Summary_Report
END
END