October 18, 2007 at 11:37 am
We have a function that is referenced in multiple stored procedures. We want to expand it's functionality for one of those procedures, and it will require passing one more parameter to the function. Will using an optional parameter mean that we don't have to change the reference in all of the other procedures?
For example, the reference currently is: SET @var1 = dbo.GetValue(x,y)
And in the one procedure we want to use: SET @var1 = dbo.GetValue(x,y,z)
And in the function:
function dbo.GetValue
@x int,
@y int,
@z int = NULL
...
Is that possible? Is there more to it than that?
October 18, 2007 at 3:49 pm
There's a way, but not quite what you'd expect. You define the function as you normally would, with a default value set on the optional param's, but when you call it and you wish to NOT pass it one of the parameters, put the word DEFAULT. Example:
create function dbo.bobby2 (@a int, @b-2 int, @C int = null) --@c is "optional"
returns int
as
begin
return @a*@b+isnull(@c,0)
end
Usage:
select dbo.bobby2(1,2,DEFAULT)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 18, 2007 at 5:08 pm
Excellent. That's just what I needed. Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply