Passing default parameter value vs not passing parameter at all?

Question

Here's what I want to do:

Given a table

PeopleOutfit (id int primary key, boots int, hat int)

And a stored procedure

UpdateOutfit @id int, @newBoots int = null, @newHat = null

Is there a way to tell whether I called this procedure as

exec UpdateOutfit @id=1, @newBoots=null, @newHat=5

effectively telling that the person with id of 1 must now be barefoot and wear fifth hat from

exec UpdateOutfit @id=1, @newHat=5

that instructs this person to wear fifth hat keeping his current boots?

In other words, I want to tell (within the stored procedure) if "the default value was used because it was not specified" from "I explicitly called this procedure passing the value that happens to be the same as default one".

I know there are several ways of accomplishing what I want to do such as passing XML or bitmask of fields being updated, but for the moment I just want to make sure whether this exact technique is possible or not.

Edit: Passing reserved values does not work for fields with small range types such as bit. Overloading procedures is also an option that's not acceptable. Creating user-defined type that extends NULL paradigm with additional "NotAValue" value might be an answer, but I need some more guidance on how to implement it.

Answer 1

no, the default null "looks" the same as a passed in null

possibly make your default -1 and use logic to do something different.

Answer 2

My guess is no, you can't tell those two things apart.

My suggestion is to use a default value that you would never pass in as an argument. i.e. if the default is null, then maybe you could pass in 0 as the value for @newBoots

Answer 3

Strictly, no, there's no real facility to accomplish this. You could, however, try using some sort of reserved value for the parameter (a very small negative number, for example) to indicate this.

Answer 4

Never done this myself; introduced the 3 state bit (using an integer) into some code to handle the bit situation. I don't have access to a sql server but i do like latteral thinking sometimes; but i think you might be able to figure it out ysubg a string manipulation over some management views / functions. You would need to run with a heck of a lot of privilege but if its absolutely neccessary i don;t see why you cant work it out from st.text using something like this

SELECT  
    st.text
FROM
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle) AS st
WHERE
    r.session_id = @@SPID

Answer 5

As stated, TSQL doesn't distinguish between supplying the default value and not supplying a value. I think the engine basically substitutes the default values for any missing parameters (or params called with the DEFAULT keyword.)

Instead, use 0 as "No Hat", and NULL as no parameter specified. This is the prefered use of NULL, where it means value unknown or not specified. By using NULL as "No Hat", you've co-opted it into adding an extra value to the range of your data type.

Think of it in terms of the BIT datatype. The datatype is defined to represent a binary value (1 or 0, or T/F if you prefer to think of it as a boolean.) By treating NULL as a valid value, you have extended the datatype beyond the binary options (now have three options, 1/0/NULL.) My recommendation is always that if you find you've run out of values in the current datatype, you're using too small a type.

Back to the stored procedure calling; if you set your default values to NULL, and treat NULL as unset or not specified, then callers should always specify a non-null value when calling the proc. If you get a NULL, assume they didn't supply a value, supplied a NULL, or used the DEFAULT keyword.