Handling optional parameters in stored procedure

I have a stored procedure, say "Test" with an optional parameter @Param1 int = NULL. In the procedure, the value of @Param1 is used to update the value of some column in some table, if the caller provided a value. If the parameter is not provided, the column is not updated. Unfortunately, that column allows NULLs, so that the caller isn't able to set the column value to NULL. So, the question is: Is the procedure able to distinguish between the following two calls?

EXEC Test -- intended meaning: don't update the column

EXEC Test @Param1 = NULL -- intended meaning: set the column to NULL

Of course, the procedure can check if @Param1 IS NULL. But can it determine if the parameter has been provided at all?

Maybe something like this might work:-

CREATE PROC dbo.Test @Param1 INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS
    (
        SELECT 1
        FROM sys.dm_exec_requests AS ER
            CROSS APPLY sys.dm_exec_input_buffer(ER.session_id, ER.request_id) AS IB
        WHERE ER.session_id = @@SPID
              AND IB.event_info LIKE '%@Param1%'
    )
    BEGIN
        IF @Param1 IS NULL
        BEGIN
            RAISERROR('@Param1 was supplied as NULL', 0, 1) WITH NOWAIT;
        END;
        ELSE
        BEGIN
            RAISERROR('@Param1 was supplied as a non-NULL value', 0, 1) WITH NOWAIT;
        END;
    END;
    ELSE
    BEGIN
        RAISERROR('@Param1 was not supplied and defaulted to NULL', 0, 1) WITH NOWAIT;
    END;
END;
GO

Test:-

EXEC dbo.Test @Param1 = NULL;
GO
EXEC dbo.Test @Param1 = 123;
GO
EXEC dbo.Test;
GO

Results (on 15.0.4102.2):-
@Param1 was supplied as NULL
@Param1 was supplied as a non-NULL value
@Param1 was not supplied and defaulted to NULL


@@SPID and CURRENT_REQUEST_ID can be used to provide the parameters to sys.dm_exec_input_buffer. No special permissions are needed to use that DMV for the current session. More rights are needed to view details for other sessions.

StackExchange Database Administrators Q#295650, answer score: 5