November 4, 2008 at 7:21 pm
Hello..
How to create a user define function with optional parameter ?
I have set the default value = 0, but still get an error.
Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.CalculateNumber.
I want to create a function like this
----------------------------------------
CREATE FUNCTION CalculateNumber(
@Num1 int = 0,
@Num2 int =0
)
RETURNS int
AS
BEGIN
DECLARE @Result INT
SET @Result = @num1 + @num2
RETURN @Result
END
----------------------------------
and SELECT with no parameter
-----------------------------
select dbo.CalculateNumber()
-----------------------------
the result should be 0
Please help me....
November 4, 2008 at 9:57 pm
You have to use the "DEFAULT" keyword:
select dbo.CalculateNumber(DEFAULT, DEFAULT)
Granted, not very useful, but that's how it works. 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 10:16 pm
Thank you it works..
but the problem is..
I have a function (other function, not CalculateNumber) that have been used in many View and SP,
then I need to add a new parameter.
Can I add parameter to this function without alter all View/SP that have used it, just like add an optional parameter to SP ?
Thanks
November 4, 2008 at 10:26 pm
Sadly, no.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2009 at 10:25 am
RBarryYoung (11/4/2008)
Sadly, no.
Sadly is right. What a disappointment.
January 13, 2011 at 8:35 am
RBarryYoung (11/4/2008)
Sadly, no.
Do you know if there are plans to change that in 2008+?
I have the same problem on a 2k5 reporting project with the function "only" being used in 20 or so places... Quite annoying.
February 3, 2011 at 11:10 am
Consider creating a new UDF with your new optional parameter, then rewriting your original UDF to call that new UDF.
Example:
You had this:
dbo.CustomFunction1(parameter1, parameter2)
Create this:
dbo.CustomFunction2(parameter1, parameter2, parameter3)
note the difference in naming... CustomFunction1 and CustomFunction2
Then rewrite CustomFunction1 to call CustomFunction2 with the default value given for parameter3.
In the end CustomFunction1 could look something like this:
CREATE FUNCTION dbo.CustomFunction1 (@parameter1 int, @parameter2 int)
RETURNS int
AS
BEGIN
DECLARE @parameter3default int
SELECT @parameter3default = 0
RETURN dbo.CustomFunction2(@parameter1, @parameter2, @parameter3default)
END
That technique allows all of your existing code to continue to use CustomFunction1.
However, you don't have to maintain separate logic for CustomFunction1 and CustomFunction2.
All of the actual logic is in CustomFunction2. CustomFunction1 becomes the SQL approximation of an overloaded function. It could also be thought of as a shortcut.
The only downside to this is the theoretical overhead involved in SQL calling UDFs. Whether or not this technique actually causes a visible performance hit is up to you for testing. A microscopic delay, if any at all, will probably not be noticable if you are already using UDF calls. If it is a significant delay, then the proper technique is to optimize your code by updating your original UDF with the new parameter, then updating ALL of your code calls to that UDF.
February 3, 2011 at 5:59 pm
Yes, I think that is smart solution.
But I already altered all SPs and Views :-).
I'll do that, if I have same problem like this.
Thanks
February 4, 2011 at 3:07 pm
Better yet, stop using UDFs so you don't get hurt by the MANY ways they can harm you!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2011 at 11:41 am
Alternatively, you could use one untyped XML parameter which can hold all your parameters. If you want to, you could make this typed XML - see XML Schema Collections for information on how to do this.
For example,
IF OBJECT_ID('udf_Sample','FN') IS NOT NULL DROP FUNCTION dbo.udf_Sample
GO
CREATE FUNCTION dbo.udf_Sample(@Params XML) RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Help BIT = 0
DECLARE @Text NVARCHAR(MAX) = ''
DECLARE @Result NVARCHAR(MAX) = ''
/* Extract the parameters from the XML */
SELECT@Help = COALESCE(frag.value('@help[1]','BIT'),frag.value('@HELP[1]','BIT')),
@Text = frag.value('.[1]','NVARCHAR(MAX)')
FROM@params.nodes('root') XML(frag)
IF @Help = 1
SET @Result = 'PRINT dbo.udf_Sample(''<root>Your Text Here</root>'')'
ELSE
SET @Result = @Text
RETURN @Result
END
GO
PRINT dbo.udf_Sample('<root help="1" />')
PRINT dbo.udf_Sample('<root>Your Text Here</root>')
April 12, 2011 at 7:11 am
Better still, write function2 to call function 1 when only 2 parameters were passed. In that way, the existing code can continue to use function1 and the new code can use function2. Arguably, function1 does what it always did, and function2 does something different with a 3rd parameter.
You had this:
dbo.CustomFunction1(parameter1, parameter2)
Create this:
dbo.CustomFunction2(parameter1, parameter2, parameter3)
CREATE FUNCTION dbo.CustomFunction2 (@parameter1 int, @parameter2 int, @parameter3 int = null)
RETURNS int
AS
BEGIN
declare @return int
if @parameter3 is null
set @return = dbo.CustomFunction1(@parameter1, @parameter2)
else
begin
-- code for dealing with 3 parameters
set @return =
end
END
April 12, 2011 at 7:16 am
freddie-897605 (4/12/2011)
Better still, write function2 to call function 1 when only 2 parameters were passed. In that way, the existing code can continue to use function1 and the new code can use function2. Arguably, function1 does what it always did, and function2 does something different with a 3rd parameter.You had this:
dbo.CustomFunction1(parameter1, parameter2)
Create this:
dbo.CustomFunction2(parameter1, parameter2, parameter3)
CREATE FUNCTION dbo.CustomFunction2 (@parameter1 int, @parameter2 int, @parameter3 int = null)
RETURNS int
AS
BEGIN
declare @return int
if @parameter3 is null
set @return = dbo.CustomFunction1(@parameter1, @parameter2)
else
begin
-- code for dealing with 3 parameters
set @return =
end
END
Dude that's the problem we're having here.. we can't use default params in functions.
April 12, 2011 at 8:41 am
freddie-897605 (4/12/2011)
Better still, write function2 to call function 1 when only 2 parameters were passed. In that way, the existing code can continue to use function1 and the new code can use function2. Arguably, function1 does what it always did, and function2 does something different with a 3rd parameter.You had this:
dbo.CustomFunction1(parameter1, parameter2)
Create this:
dbo.CustomFunction2(parameter1, parameter2, parameter3)
CREATE FUNCTION dbo.CustomFunction2 (@parameter1 int, @parameter2 int, @parameter3 int = null)
RETURNS int
AS
BEGIN
declare @return int
if @parameter3 is null
set @return = dbo.CustomFunction1(@parameter1, @parameter2)
else
begin
-- code for dealing with 3 parameters
set @return =
end
END
Freddie... you just essentially copied my reply from 2 months ago... but you modified it... and you did it wrong.
The whole point of this thread is that one cannot provide a default value for a UDF parameter (as you did with @parameter3 int = null). In addition, your assumption of leaving CustomFunction1 unchanged and adding new code to CustomFunction2 creates a divergence and increases code complexity. If CustomFunction1 is left unchanged, and all new functionality is added to CustomFunction2, then the developer will have to maintain separate logic in completely separate UDFs that are not related to each other. Creating a new CustomFunction2(@p1, @p2, @p3), then remapping CustomFunction1 to call CustomFunction2 allows all existing code to continue to work, allows specific calls to be "upgraded" to the new 3rd parameter, and it maintains all logic in a single UDF with the older one serving as a compatability pointer.
What you are saying could work for stored procedures (technically), but I still feel you should concentrate all logic in the object with 3 params and rewrite the older ones to "pointer" to the new one.
January 4, 2012 at 9:56 am
Here's a solution that does not involve modifying the name of the function or creating a new function.
It is an attempt at avoiding spaghetti code.
Because a new function with a new name for every parameter thought of after the original CREATE FUNCTION can get messy.
I think, because you must use the word DEFAULT, optional UDF parameters are a pointless way to make new code backward compatible.
So, my opinion is to bite the bullet and change the calling code and don't create a wrapper UDF unless you don't have access to the calling code.
(Worrying about submitting too many change requests to the manager is not a good enough reason, in my opinion, to start cooking spaghetti (not saying you did that))
The easiest way I can see to add another parameter with default functionality is to set the default value IN THE UDF BODY, AND use an optional parameter.
This gives the maximum flexibility to callers.
That way, if you have a variable going into the UDF from the calling sql, it can be a special value like NULL and if it is, you can SET it to something more useful.
Or you can use DEFAULT. The point is that if you are using a variable to hold the optional parameter, you can't store the keyword DEFAULT in it.
This way, when calling with a variable, you don't have to know, remember or care what the default value is in the calling code.
ALTER function [dbo].[Schema.TableColumns]
(
@TableName SYSNAME,
@Schema SYSNAME = 'dbo'
)
returns @TableColumns table
(ID int,
Name varchar(max),
Type varchar(max),
PrimaryKey bit,
Computed bit
)
as begin
/*
SELECT * FROM [dbo].[Schema.TableColumns]('state') -- this isn't going to work any more
SELECT * FROM [dbo].[Schema.TableColumns]('state','workflow') -- this works
SELECT * FROM [dbo].[Schema.TableColumns]('state',NULL) -- this works
SELECT * FROM [dbo].[Schema.TableColumns]('state',DEFAULT) -- this works
SELECT * FROM [dbo].[Schema.TableColumns](@MyTableName,@MySchemaName) -- this works if schema contains value or IS NULL
*/
IF@Schema IS NULL
SET@Schema
='dbo'
-- more code...
RETURN
END
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply