USE [Melville]
GO
/****** Object:  StoredProcedure [dbo].[SP_EnabledCategories]    Script Date: 06/28/2013 14:17:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_EnabledCategories] 
	-- Add the parameters for the stored procedure here
	@ShowId int = 0, 
	@showdate datetime = 0,
    @orderdate datetime = null,


    
 if (@orderdate = null) 
 BEGIN
    AS

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

    -- Insert statements for procedure here
select @showdate = builddate from show where showid=@showid
SELECT TOP 9999999 [CategoryId]
      ,[ParentCategoryId]
      ,[Description]
      ,[DisplayOrder]
      ,[FilePath]
      ,[FileName]
      ,[IsDeletedOnOnlineOrdering]
      ,[IsDeletedOnClick]
      ,[StandInfoRequired]
      ,[DaysBeforeBuildDateToDisable]
      ,[CanHaveSeperateDeliveryAddress]
      ,datediff(d,getdate(), case when @showdate)    
  FROM [Melville].[dbo].[Category]
  where 
  ((CategoryId not in (select CategoryId from ShowDisabledCategory where ShowId=@Showid))
   )
  and (datediff(d,getdate(),@showdate) >DaysBeforeBuildDateToDisable)
  
  order by parentcategoryid, DisplayOrder 

END
ELSE
begin
AS

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

    -- Insert statements for procedure here
select @orderdate = CustomDate from show where showid=@showid
SELECT TOP 9999999 [CategoryId]
      ,[ParentCategoryId]
      ,[Description]
      ,[DisplayOrder]
      ,[FilePath]
      ,[FileName]
      ,[IsDeletedOnOnlineOrdering]
      ,[IsDeletedOnClick]
      ,[StandInfoRequired]
      ,[DaysBeforeBuildDateToDisable]
      ,[CanHaveSeperateDeliveryAddress]
      ,datediff(d,getdate(), case when @orderdate)    
  FROM [Melville].[dbo].[Category]
  where 
  ((CategoryId not in (select CategoryId from ShowDisabledCategory where ShowId=@Showid))
   )
  and (datediff(d,getdate(),@orderdate) >DaysBeforeBuildDateToDisable)
  
  order by parentcategoryid, DisplayOrder 

END

USE [Melville]
GO
/****** Object:  StoredProcedure [dbo].[SP_EnabledCategories]    Script Date: 06/28/2013 14:17:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_EnabledCategories] 
	-- Add the parameters for the stored procedure here
	@ShowId int = 0, 
	@showdate datetime = 0,
    @orderdate datetime = null
AS
select @showdate = case when @orderdate is null then builddate else CustomDate end from show where showid=@showid

SELECT TOP 9999999 [CategoryId]
      ,[ParentCategoryId]
      ,[Description]
      ,[DisplayOrder]
      ,[FilePath]
      ,[FileName]
      ,[IsDeletedOnOnlineOrdering]
      ,[IsDeletedOnClick]
      ,[StandInfoRequired]
      ,[DaysBeforeBuildDateToDisable]
      ,[CanHaveSeperateDeliveryAddress]
      ,datediff(d,getdate(), case when @showdate)    
  FROM [Melville].[dbo].[Category]
  where 
  ((CategoryId not in (select CategoryId from ShowDisabledCategory where ShowId=@Showid))
   )
  and (datediff(d,getdate(),@showdate) >DaysBeforeBuildDateToDisable)
  
  order by parentcategoryid, DisplayOrder 

GO