1

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

2

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.

  • And BoL is again not precise: The default value must be a constant or it can be NULL. Nothing about letting you reference any of the already parsed parameters. – Andriy M Mar 18 '15 at 11:00
  • As for the Connect item, I think it's about a different issue. It's about using functions when calling a stored procedure, and the OP wants to use functions in a specific place of a declaration. – Andriy M Mar 18 '15 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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