Have Geek Brain

Passing a default value for the vs parameter passing no parameter at all?

Here's what I want to do:

For table

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

And the stored procedure

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

Is there a way to find out if I called this procedure like

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

effectively saying that a person with an id of 1 should now be barefoot and wear a fifth hat from

exec UpdateOutfit @id=1, @newHat=5

who instructs this man to wear the fifth hat while keeping his current boots?

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

I know there are several ways to accomplish what I want to do, such as passing in XML or a bitmask of the fields that are being updated, but for now I just want to make sure if this exact technique is possible or not.

Edit: Passing reserved values ​​doesn't work for fields with small range types like bits. Reloading procedures are also unacceptable. Creating a custom type that extends the NULL paradigm with an additional "NotAValue" value might be the answer, but I need some more guidance on how to implement it.

0


a source to share


5 answers


My guess is no, you cannot tell the difference between the two.



My suggestion is to use the default, which you will never pass as an argument. that is, if the default null

is then perhaps you can pass 0

as the value for@newBoots

+1


a source


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



it's possible to make your default -1 and use logic for something else.

+1


a source


Strictly, no, there is no real means to achieve this. However, you can try to use some kind of reserved value for the parameter (for example, a very small negative number) to indicate this.

0


a source


Never did it myself; introduced status bit 3 (using an integer) into some code to handle the bit situation. I don't have access to sql server, but sometimes I think about the latter; but i think you could figure out how to do this, manipulate the string by some view / control function. You will need to run with huge privilege, but if absolutely necessary, I don't understand why you cannot process it 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
0


a source


As stated, TSQL does not differentiate between supplying a default and not supplying a value. I think the engine basically replaces the defaults for any missing parameters (or params called with the DEFAULT keyword.)

Instead, use 0 as "No hat" and NULL as no parameter. This is the preferred use of NULL, where it means a value, unknown or not specified. By using NULL as "No Hat", you included it in adding an extra value to the range of your datatype.

Think about it in terms of the BIT datatype. The data type is defined as representing a binary value (1 or 0, or T / F if you prefer to think of it as boolean). By treating NULL as a valid value, you extended the datatype beyond binary parameters (now three choices, 1/0 / NULL.) My recommendation is always that if you find that you run out of values ​​in the current datatype, you use too small type.

Return to the call to the stored procedure; if you set the defaults to NULL and consider NULL to be disproportionate or unspecified, then callers should always specify a nonzero value when calling proc. If you get NULL, assume they didn't supply a value, provided NULL, or used the DEFAULT keyword.

0


a source






More articles:


All Articles