"Vic Y" <vi********@hotmail.com> wrote in message
news:c2**************************@posting.google.c om...
Hi,
I am trying to call a user defined function(UDF) from a stored proc,
using a default parameter in the called UDF (e.g. @bid_price_type int
= 0 ). However the calling stored proc complains that the UDF is
expecting a 2nd parameter when I only provide the @test parameter
value. Are default parameter values supported in UDF's?
I wld really appreciate anybody's help. Thankyou in advance.
UDF code:
CREATE FUNCTION get_bid_price (@test int, @bid_price_type int = 0)
RETURNS decimal(18, 4) AS
BEGIN
declare @x decimal(18, 4)
if (@bid_price_type = 0)
begin
select @x = fieldName
from
tableName
end
else
begin
select @x = fieldName2
from
tableName2
end
return @x
END
' Calling Stored Proc code
CREATE PROCEDURE test
AS
declare @x decimal(18, 4)
set @x = dbo.get_bid_price(1)
select @x
GO
thanks,
Vic Y
Default parameters are supported (see the discussion of @parameter_name
under CREATE FUNCTION in BOL), but unfortunately you need to specify the
DEFAULT keyword in place of the value - you can't just leave it out like you
can with a stored proc:
select dbo.MyFunc(DEFAULT)
And since DEFAULT is a keyword, not a string, you can't use it in an
expression, so this won't work either:
select dbo.MyFunc(case when @i is null then DEFAULT else @i end)
Unless I'm missing something, I'd say that default values in functions are
not very useful in most cases (except perhaps as documentation), because the
calling process has to have logic to call either func(value) or
func(DEFAULT):
if @i is null
set @x = dbo.func(DEFAULT)
else set @x = dbo.func(@i)
For one parameter it might not be a huge issue, but if you have multiple
parameters with defaults, then the number of possible combinations quickly
becomes unmanageable.
Simon