Henry8484 2016-05-05 03:49 采纳率: 0%
浏览 1260

SQL SERVER Procedure請教

環境: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


  • 写回答

1条回答

  • 普通网友 2016-05-05 04:47
    关注
    评论

报告相同问题?

悬赏问题

  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 怎么在stm32门禁成品上增加记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号