473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

user defined functions & default parameters

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
Jul 23 '05 #1
2 23329

"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
Jul 23 '05 #2
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
Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: dawatson833 | last post by:
I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure. If I...
88
by: Mike | last post by:
Is there a way to determine what a user's default email client is? I read a post from 3 years ago that said no. I guess I'm hoping something has come along since then.
5
by: Ed Havelaar | last post by:
I have a cool function that I want to use as a default value for a column in my table. But I can't because apparently Access doesn't allow user defined functions in expressions for default values....
1
by: Oodini | last post by:
Hello, Could anyone explain me the following preprocessor sentences: ---------------------------------------------------------------- #if (defined(__STDC__) && !defined(NO_PROTOTYPE)) ||...
3
by: chreo | last post by:
I have user-defined function in MSSQL which returns Table (with 10 columns) (sorry for Polish names) CREATE FUNCTION PACZKI_Z_AKCJI (@AKCJA_ID int) RETURNS TABLE RETURN SELECT TOP 100...
1
by: dirk van waes | last post by:
Hello everyone, Being complete newbie in asp.net I am trying to make an example which works with a very simple database. First I made my project in VS- vb.net, draging an oledbconnection and an...
6
by: karthi | last post by:
hi, I need user defined function that converts string to float in c. since the library function atof and strtod occupies large space in my processor memory I can't use it in my code. regards,...
0
by: sanou | last post by:
I was trying to create a user defined function for Excel with the following features: Check target cell: -if target is empty (whether originally empty or data has since been cleared from the...
2
by: aj | last post by:
SQL Server 2005 64-bit 9.00.3042 SP2 When I map a database user to a login, I can specify a default schema for that user. After that, any SQL from that user w/o an explicit schema will be...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.