Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Setting default parameters for a SQL Server stored procedure

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

like image 291
Maddie Avatar asked Jul 06 '26 05:07

Maddie


1 Answers

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
like image 166
Ruslan K. Avatar answered Jul 09 '26 03:07

Ruslan K.



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!