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?
>>>>
>>>>Say I have this SP:
>>>>
>>>>CREATE PROCEDURE MyProc
>>>> @One char(1) = NULL,
>>>> @Two char(2) = NULL,
>>>> @Three char(3) = NULL
>>>>AS
>>>> blah, blah, blah
>>>>
>>>>OK, now say I call it like this:
>>>>
>>>>Three = 'abc'
>>>>EXEC MyProc ?Three
>>>>
>>>>In the SP, I know that I can check that values of @One and @Two and since they are both NULL, they may or
may not have been passed as parameters, since I may have actually assigned the NULL value to them intentionally:
>>>>
>>>>One = NULL
>>>>Three = 'abc'
>>>>EXEC MyProc ?One, ?Three
>>>>
>>>>In this second case ... is there anyway to tell that @One was passed as a parameter, but @Two was not?
>>>>
>>>>TIA,
>>>>~~Bonnie