Post reply

repeating a stored proc x times

  • April 11, 2005 at 7:27 pm

    #76276

    I have a proc (RptGPHistUpdt) that fills a reporting history table (RptGPHJVHist) for each week that is listed in a WkPeriod table. How can I automatically run my proc for all of the past weeks listed in my WkPeriod table (there are currently 40). For each Week, I enter a processed date so that I can track what weeks have had their history completed. Once I get all of the history filled, I'll just run a Job

    CREATE TABLE [WkPeriod] (

     [WkPeriodID] [smallint] IDENTITY (1, 1) NOT NULL ,

     [Group1] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [StartDate] [smalldatetime] NOT NULL ,

     [EndDate] [smalldatetime] NOT NULL ,

     [ProcessedDate] [smalldatetime] NULL ,

     CONSTRAINT [PK__SpmFinPeriod__6C70767B] PRIMARY KEY  CLUSTERED

     (

      [WkPeriodID]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • April 11, 2005 at 8:38 pm

    #552010

    Does the [ProcessedDate] field get updated once your stored procedure is run?

    If so you could create a "driver" stored procedure that use a while loop to call your other stored proc.

    EG:

    -- prime the while loop
SELECT TOP 1
  @wk = WkPeriodID ( or whatever your stored proc needs
FROM WkPeriod
WHERE ProcessedDate IS NULL
ORDER BY WkPeriodID
    WHILE @wk IS NOT NULL
BEGIN
    --call your stored proc
    EXEC dbo.RptGPHistUpdt @wk, ( and other parameters? )

    -- get the next value for the while loop
    SELECT TOP 1
      @wk = WkPeriodID ( or whatever your stored proc needs
    FROM WkPeriod
    WHERE ProcessedDate IS NULL
    ORDER BY WkPeriodID

END

     

    --------------------
    Colt 45 - the original point and click interface

  • April 13, 2005 at 11:05 am

    #552495

    Thanks, that was exactly what I needed

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply