user defined functions & default parameters

[Vic Y]

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

[Simon Hayes]

"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

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

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)

It's correct that default parameters with functions is bulky, but the above
scenario would be

if @i is null
set @x = dbo.func()
else set @x = dbo.func(@i)

in a more "normal" syntax. I know of know language where I can pass an
expression as a parameter, and the expression can evaluate to "actually
I am not passing this parameter at all".

The major drawback with the UDF syntax, is that you cannot add a new
parameter to a UDF without affecting existing callers.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp