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币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
pager procedure for sql server
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
SQL Server blocked access to procedure 'sys.xp_cmdshell' ...
在sa帐户下运行下列命令,即可解决题目中的SQLServer错误sp_configure show advanced options, 1GORECONFIGUREGOsp_configure xp_cmdshell, 1GORECONFIGUREGO 
SQL Procedure 建立
--********************************************************************************************* -- ABOUT SQL PROCEDURE, -- 关于SQL存储过程的介绍,建立和使用方法 --***************************************************
Sql Server Procedure 的书写格式....
存储过程的书写格式:create procedure pro_name  ----创建procedure名(@parame_1 datatype(length),@parame_2 datatype(length),@parame_3 datatype(length))as  set nocount on ----指示存储过程不返回查询影响的行数---定义要用的
SQL Server问题之The remote procedure call failed. [0x800706be]
打开SQL Server Confirmation Manager    在SQL Server Services下出现错误The remote procedure call failed. [0x800706be]     问题原因猜测 本机上软件的安装顺序为VS 2008 -> SQL Server 2008 R2 -> VS 2012,有可能是由于VS 2
如何查看加密过的SQL Server procedure
http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/
SQL Server中存储过程Stored Procedure创建及C#调用
存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL语句;可供应用程序直接调用。使用存储过程有以下几个优点: 1、执行速度比普通的SQL语句快 2、便于集中控制 3、可以降低网络的通信量 4、保证数据库的安全性和完整性 5、灵活性创建存储过程 可以采用Transact-SQL语句来创建存储过程Stored Procedured。在Microsoft SQL Server Ma
SQL Procedure
SQL一些存储过程示例 演示了存储过程基本使用。 以及演示了在存储过程中使用:临时表、函数、事务、嵌套查询、游标、异常处理、数据库的身份权限等。 详细使用见示例。
sql查询查所有存储过程(stored procedure)
 sql查询查所有存储过程(stored procedure)   INFORMATION_SCHEMA.ROUTINES view Requirements: Microsoft SQL Server 2000 or later You can use the INFORMATION_SCHEMA.ROUTINES view to retrieve information
SQL Server存储过程基本语法
转自http://c21.cnblogs.com/archive/2006/05/08/393779.html存储过程的概念       SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成,以实现某个任务,这种方法就是存储过程。       存储过程是SQL语句和可选控制流语句的预编译集合,存储在数据库中,可由应用程序通过一个调用执行,而