0

i am trying to figure our sql syntax and my initial challenge was to call a stored procedure name GetPIValueAverageTime and write the float value to an table named actuals. and it created the row of data for that inparticular production date. in sql how can i iterate through two dates the production date entered to the 1st of that month and enter that date that the iteration is on into the exec of the stored procedure in order to populate date from the particular date ? this is just the first part i need to add all the float values i get from those dates populate and fill and month to date average but ill handle that later i just what to populate the table with data in an iteration from the prod date to the 1st of the month. this is what i have so far please excuse the mess. i tried to do this with a while loop but it inserts first row for the first day of the month then hangs on "executing query" and i have to force kill it.

USE [OperationsView]
GO
/****** Object:  StoredProcedure [Interface].[proc_GetChemicalDaily] Script    Date: 1/30/2017 4:03:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Daily Chemical>
-- =============================================
ALTER PROCEDURE [Interface].[proc_GetChemicalDaily]
-- Add the parameters for the stored procedure here

@ProductionDate As DateTime,
@Debug BIT = 1

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- pi sproc variables Declaration
DECLARE @result As Float
DECLARE @tag_in As varchar(80)
DECLARE @pi_server As varchar(50)
DECLARE @enddate As DateTime
DECLARE @FCChemGALPERMIN float
DECLARE @CAUSTICDAILYUSAGE As Decimal(16, 8)
DECLARE @constant As Decimal(16, 8)
DECLARE @BegMonth As DateTime

--Actual variables for insertion
DECLARE @Group As VARCHAR(200)
DECLARE @Item As VARCHAR(200)
DECLARE @Total As DECIMAL(20, 4)
DECLARE @Days As Int
DECLARE @Type As VARCHAR(1)


--Pi Sproc variable setting
   SELECT @tag_in = 'I-FC-835'
   SELECT @pi_server = 'valpi'
   SET @constant = (18318.0/2000.0)*.5
   SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

   --Setting Actual insertion variables
   SET @Group = 'CHEMICAL'
   SET @Item = 'CAUSTIC CHEM'
   SET @Days = 1
   SET @Type ='a'
   --create temp table to grab decimal value
   create table #temp
   (
    float_col   float,
    percent_col float
   )
--Grabbing gal/min value from PI
While @productionDate >= @BegMonth

     SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
     INSERT into #temp(float_col, percent_col)
     EXEC Interface.proc_GetPIValueAverageTime @result, @tag_in, @BegMonth, @endDate, @pi_server, @Debug 
     SELECT @FCChemGALPERMIN = float_col FROM #temp

  -- daily dry ton usage calculation
  IF @FCChemGALPERMIN > .5
 SET @CAUSTICDAILYUSAGE = cast(@FCChemGALPERMIN*@constant as numeric(38,10))
  ELSE
 SET @CAUSTICDAILYUSAGE = 0.0000

--Inserting in Actuals table
IF @Debug = 1

begin
    INSERT INTO [OperationsView].[dbo].[Actuals]
            ([Group]
            ,[Item]
            ,[ProductionDate]
            ,[Days]
            ,[Total]
            ,[Type])
        VALUES
            (@Group
            ,@Item
            ,@ProductionDate
            ,@Days
            ,@CAUSTICDAILYUSAGE
            ,@Type);                    
end 

DROP TABLE #temp
Set @BegMonth = DATEADD(DAY, 1, @BegMonth)
 END

closed as off-topic by Aaron Bertrand Jan 30 '17 at 23:51

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

1

I reduced your statement down to the simplest possible piece of code, and found the exact error. I removed everything except the loop, and got to this:

CREATE PROCEDURE dbo.test
(
    @ProductionDate As DateTime
    , @Debug BIT = 1
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @BegMonth As DateTime

    SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

    WHILE @productionDate >= @BegMonth
        SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
END

The WHILE statement does not have BEGIN directly afterwards. This means the WHILE loop is essentially this:

WHILE @productionDate >= @BegMonth SELECT @endDate = DATEADD(DAY, 1, @BegMonth)

and nothing else. Just, loop forever.

To fix it, make your code like this:

USE [OperationsView]
GO
/****** Object:  StoredProcedure [Interface].[proc_GetChemicalDaily] Script    Date: 1/30/2017 4:03:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Daily Chemical>
-- =============================================
ALTER PROCEDURE [Interface].[proc_GetChemicalDaily]
-- Add the parameters for the stored procedure here

@ProductionDate As DateTime,
@Debug BIT = 1

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- pi sproc variables Declaration
DECLARE @result As Float
DECLARE @tag_in As varchar(80)
DECLARE @pi_server As varchar(50)
DECLARE @enddate As DateTime
DECLARE @FCChemGALPERMIN float
DECLARE @CAUSTICDAILYUSAGE As Decimal(16, 8)
DECLARE @constant As Decimal(16, 8)
DECLARE @BegMonth As DateTime

--Actual variables for insertion
DECLARE @Group As VARCHAR(200)
DECLARE @Item As VARCHAR(200)
DECLARE @Total As DECIMAL(20, 4)
DECLARE @Days As Int
DECLARE @Type As VARCHAR(1)


--Pi Sproc variable setting
   SELECT @tag_in = 'I-FC-835'
   SELECT @pi_server = 'valpi'
   SET @constant = (18318.0/2000.0)*.5
   SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

   --Setting Actual insertion variables
   SET @Group = 'CHEMICAL'
   SET @Item = 'CAUSTIC CHEM'
   SET @Days = 1
   SET @Type ='a'
   --create temp table to grab decimal value
   create table #temp
   (
    float_col   float,
    percent_col float
   )
--Grabbing gal/min value from PI
While @productionDate >= @BegMonth
BEGIN
     SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
     INSERT into #temp(float_col, percent_col)
     EXEC Interface.proc_GetPIValueAverageTime @result, @tag_in, @BegMonth, @endDate, @pi_server, @Debug 
     SELECT @FCChemGALPERMIN = float_col FROM #temp

  -- daily dry ton usage calculation
  IF @FCChemGALPERMIN > .5
 SET @CAUSTICDAILYUSAGE = cast(@FCChemGALPERMIN*@constant as numeric(38,10))
  ELSE
 SET @CAUSTICDAILYUSAGE = 0.0000

--Inserting in Actuals table
IF @Debug = 1

begin
    INSERT INTO [OperationsView].[dbo].[Actuals]
            ([Group]
            ,[Item]
            ,[ProductionDate]
            ,[Days]
            ,[Total]
            ,[Type])
        VALUES
            (@Group
            ,@Item
            ,@ProductionDate
            ,@Days
            ,@CAUSTICDAILYUSAGE
            ,@Type);                    
end 

DROP TABLE #temp
Set @BegMonth = DATEADD(DAY, 1, @BegMonth)
 END
END

Not the answer you're looking for? Browse other questions tagged or ask your own question.