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

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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. Commented Mar 18, 2015 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. Commented Mar 18, 2015 at 12:15

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.