Take a look at http://www.sqlmag.com/Forums/Thread.cfm?CFApp=57&Thread_ID=88559 for similar discussion.

>No special reason really ... mainly just thinking out loud. Say I had an Update SP, and I only wanted to update the columns that were passed to it. If I only pass @Three, then I only want to update the Three column ... I do *not* want to update the One and Two columns to a NULL ... which is what would happen if I didn't pass them as parameters ... unless I did something like you suggested (setting the default to something that would never be passed and then checking for that).
>
>I'm trying to avoid an Update SP that updates *every* column and instead have an SP that only updates what is sent to it.
>
>~~Bonnie
>
>
>>If you tell us why you would want to do that maybe we can offer some other way to resolve your problem.
>>
>>>Thanks, Sergey ... I was afraid that that might be the answer, since I couldn't find anything in the T-SQL docs that indicated that I might be able to do this. Oh well ... I tried. <g>
>>>
>>>Thanks,
>>>
>>>~~Bonnie
>>>
>>>
>>>>The short answer is no. If you want Null to be a legitimed value that SP can distinguish change default value to something that never can be passed as a parameter. Or add one more parameter to indicate such situation.
>>>>
>>>>>I have a quick question about optional parameters in Stored Procedures... is there any way of telling if a named parameter was actually passed to the SP?
< snip >