SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE PROC dbo.TestProc @NullParam int = NULL, @NonNullParam int AS
BEGIN

    IF @NonNullParam IS NULL
        THROW 75001, N'Procedure ''TestProc'' expects a non-NULL value for parameter ''@NonNullParam'', which was not supplied.',16; --Choose a error number appropriate for you

    SELECT @NullParam + @NonNullParam;
END;
--Works
EXEC dbo.TestProc @NullParam = 1,
                  @NonNullParam = 1;


(No column name)


2


--Works (returns NULL)
EXEC dbo.TestProc @NonNullParam = 1;


(No column name)


null


--Fails, as @NonNullParam not supplied
EXEC dbo.TestProc @NullParam = 1;
Msg 201 Level 16 State 4 Line 0
Procedure or function 'TestProc' expects parameter '@NonNullParam', which was not supplied.

--Fails, as @NonNullParam has NULL value
EXEC dbo.TestProc @NullParam = 1,
                  @NonNullParam = NULL;
Msg 75001 Level 16 State 16 Line 5
Procedure 'TestProc' expects a non-NULL value for parameter '@NonNullParam', which was not supplied.

DROP PROC dbo.TestProc;