Sql-server – use parameters in a Cte

sql server

I want to use parameters in the below cte but I get the below error messages

  • Msg 102, Level 15, State 1, Procedure X82 7 Days/by Day, Line 5
    Incorrect syntax near '('.
  • Msg 137, Level 15, State 2, Procedure X82 7 Days/by Day, Line 21
    Must declare the scalar variable "@startDate".

Once I can get the perameters to work I will turn the static pivot into a dynamic one

    ALTER Procedure [dbo].[X82 7 Days/by Day] 
            (
            @StartDate As datetime = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0)
            ,@EndDate As datetime = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0)
            )
    As
    Begin   

    ;with cte as(

    select  [CAB Part No.]
        ,[RFT/Scrap/Rework]
        ,[Process No.]
        ,[Process]
        ,[Date & Time]
        ,[Quantity]
        ,cast([Date & Time] as date) UtcDay
    from    [SFDLOG]
    where   [Date & Time] between @startDate and @EndDate
    )
    select  [CAB Part No.]
        ,[RFT/Scrap/Rework]
        ,[Process No.]
        ,[Process]
        ,[Quantity]
        ,UtcDay
    into #TempDates
    from cte

    select *
    from
    (
    select   [Quantity]
        ,[utcday]
        ,[CAB Part No.]
        ,[Process No.]
        ,[Process]
        ,[RFT/Scrap/Rework]
    from #tempDates
     ) x
    pivot
    (
    count([Quantity])
    for [utcDay]
    In ([2015-02-01],[2015-02-02],[2015-02-03],[2015-02-04],[2015-02-05],[2015-02-06],[2015-02-07])
    )p

    Order By [CAB Part No.],[Process No.]

    drop table #tempdates

End

Best Answer

The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL 
  ,@EndDate As datetime = NULL 
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);


END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.

Related Question