sqlsql-serversyntax-errorexecdateadd

SQL Server : DATEADD value in parameters is returning an error

I've built a procedure I need to run it for the last 60 days. I can't seem to figure it out.

I've got the following date parameters statements for the parameters and they return

select dateadd(Day, -60, getdate()), dateadd(Day, -1, getdate())

enter image description here

The code itself adjusts the end date to be at the very end of the day. 23:59:59.

When I do this

exec LRP_PROC 
         @start_Dt = dateadd(Day, -60, getdate()),
         @end_dt = dateadd(Day, -1, getdate()),
         @mode = 0

I get the following error

enter image description here

I can't figure out what I'm doing wrong.

Thank you.

Solution

  • SQL Server does not permit arbitrary expressions to be passed as stored procedure parameters. You need to declare and assign local variables before calling the proc.

    eg

    declare @start datetime = dateadd(Day, -60, getdate());
declare @end datetime = dateadd(Day, -1, getdate());

exec LRP_PROC @start_Dt=@start, @end_dt=@end, @mode = 0;