if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GenerateMissingProductPriceRecords]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GenerateMissingProductPriceRecords]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.GenerateMissingProductPriceRecords @AProductNumber INT AS

DECLARE @EndDate VARCHAR(8)
DECLARE @TempDate DATETIME
DECLARE @RoomTypeNumber INT

SELECT @EndDate = ProductEndDate FROM Products WITH (NOLOCK) WHERE ProductNumber = @AProductNumber



DECLARE RoomTypeCur CURSOR FOR
SELECT RoomTypeNumber FROM RoomTypes WITH (NOLOCK) 
OPEN RoomTypeCur
FETCH NEXT FROM RoomTypeCur
INTO @RoomTypeNumber
WHILE(@@Fetch_status=0)
BEGIN

  SELECT @TempDate = CONVERT(DateTime, SystemDate,112) FROM dbo.System WITH (NOLOCK)
  WHILE @TempDate <= @EndDate
  BEGIN
     IF NOT EXISTS (SELECT ProductNumber, RoomTypeNumber, DateStamp FROM ProductPrice WITH (NOLOCK) WHERE ProductNumber = @AProductNumber AND RoomTypeNumber = @RoomTypeNumber AND DateStamp = CONVERT(VARCHAR(8),@TempDate,112))
       INSERT INTO ProductPrice (ProductNumber, RoomTypeNumber, DateStamp) VALUES (@AProductNumber, @RoomTypeNumber, CONVERT(VARCHAR(8),@TempDate,112))
    SET @TempDate = @TempDate  + 1
  END

 FETCH NEXT FROM RoomTypeCur INTO @RoomTypeNumber
END

CLOSE RoomTypeCur
DEALLOCATE RoomTypeCur
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

