Use GetDate() as default parameter for SP in T-SQL not working

Question

I am trying to create a SP in TSQL that gets month as parameter (in numeral format). I would like that if the parameter be suppressed by the user then the SP will use the current month.

I tried this but it says that I am using an incorrect syntax:

CREATE PROCEDURE myproc
    @month int = select Month(GetDate())

What am I doing wrong?

Thanks!

Answer 1

You cannot pass func call as default parameter value:

CREATE PROCEDURE myproc
    @month int = select Month(GetDate())

should be:

CREATE PROCEDURE myproc
@month int = NULL
AS
BEGIN
 SET @month = COALESCE(@month, Month(GetDate()));

 SELECT @month;
END;

db<>fiddle demo

If user does not provide value for @month then by default it is NULL and COALESCE will set proper value.