2 s373785346 s373785346 于 2016.05.05 11:49 提问

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


2个回答

CSDNXIAOD
CSDNXIAOD   2016.05.05 12:01

Dynamic 'Sales by Week' Procedure in SQL Server
如何查看加密过的SQL Server procedure
SQL Server问题之The remote procedure call failed. [0x800706be]
----------------------biu~biu~biu~~~在下问答机器人小D,这是我依靠自己的聪明才智给出的答案,如果不正确,你来咬我啊!

qq_34890612
qq_34890612   2016.05.05 12:47
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!