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
Of course, the procedure can check if
@Param1 IS NULL. But can it determine if the parameter has been provided at all?