How to detemine if the default value was supplied to stored procedure?

Question

Hi,

There's a stored procedure with the parameter that may take default value

CREATE PROCEDURE [dbo].[FooSP] 
    @FooParam INT = 15
AS

So is there a way to determine whether that SP was called with or without setting the @FooParam value.

IOW, is there a way to determine if the user called

exec dbo.FooSP(15)

or

exec dbo.FooSP()

Thanks!

Answer 1

If you need to do that, you are usually better off changing the default value to some kind of sentinal value (NULL often works well in db stored procedures,) and putting some logic in the procedure to set the value to you old default (15 in this case) if NULL is received.

That way, you get the benefit of a default value, and the ability to know what was passed in.

UPDATE

If there is no possible sentinal value -- say, because the parameter is very constrained, where all possible inputs are legitimate, then you will have to either do some stack introspection, or change the function signature.

If you can change the calling code, then make the procedure with the default parameter actually a different procedure, which then calls your original procedure with the default set explicitly.

Answer 2

You might want to vote for this related Connect item - getting call stack information can be really useful to be able to instrument code, and debug situations.