I am trying to set the default value of @Day to the current day. However, I am getting a couple errors on the initial DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0) and on all the @Days below it. I thought that the @Day DateTime under the alter procedure declared @Day but I am getting the error:
Must declare the scalar variable "@Day"
Anyone know what to do?
ALTER PROCEDURE [dbo].[InsertWeeklyRuns]
@Day DateTime = DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Enddate DATETIME
DECLARE @StartDate DATETIME
DECLARE @F1Runs INT
DECLARE @F2Runs INT
DECLARE @F3Runs INT
DECLARE @F1Alarms INT
DECLARE @F2Alarms INT
DECLARE @F3Alarms INT
SET @Day = DATEADD(dd, DATEDIFF(dd, 0, @Day), 0)
SET @Enddate = CASE
WHEN @Day > DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
THEN DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
ELSE DATEADD(Day, 1, @Day)
END
SET @StartDate = @Enddate - 7
Thanks
This should work as you expected:
CREATE PROCEDURE [dbo].[InsertWeeklyRuns]
@Day DateTime
AS
BEGIN
SET NOCOUNT ON
Declare @Enddate Datetime
Declare @StartDate Datetime
Declare @F1Runs Int
Declare @F2Runs Int
Declare @F3Runs Int
Declare @F1Alarms Int
Declare @F2Alarms Int
Declare @F3Alarms Int
DECLARE @today date = CAST(GETDATE() AS date);
IF @Day IS NULL
Set @Day = @today
Set @Enddate = CASE WHEN @Day >= @today THEN @today ELSE DATEADD(Day, 1, @Day) END
Set @StartDate = @Enddate - 7
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With