4

I am looking for a solution, how to create SQL UDF with optional params.

Pseudocode for function where Param1 is necessary and Param2 may be filled (but not needed):

dbo.myFnc(Param1 int [, Param2 int])

Is there a way to create thislike function? For existing built-in sample watch the STR function

STR ( float_expression [ , length [ , decimal ] ] )
procma
  • 1,174
  • 3
  • 14
  • 24

3 Answers3

6

You need to pass all arguments to functions, unlike stored procedures. You can use the default keyword to indicate that default value is to be used for the parameter, rather than a user-specified value.

So you can create your function like this:

CREATE FUNCTION dbo.myFnc
(
 @param1 int,
 @param2 int)

...

and then call the function like this:

dbo.myFnc(Param1, default)
shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • yes, I know this. But in this case I have to fill "default" key word :( – procma Jul 24 '14 at 08:45
  • @MartinProcházka: You could always use a stored procedure instead, in which case explicitly requesting the default is not necessary. – Pieter Geerkens Jul 24 '14 at 08:48
  • 1
    Yeah, that's a problem with UDF. Stored procedures would be a good idea to overcome this, but is there a real issue that you are facing with this workaround i.e. apart from having to type out default :)? – shree.pat18 Jul 24 '14 at 08:50
  • 2
    @shree.pat18: Finding the best way :) Not only for this job, but also for upcomming ones... – procma Jul 24 '14 at 09:04
2

You can define default parameters in the create statement (= default):

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Source MSDN

So you can do something like:

CREATE FUNCTION dbo.myFnc(
 @param1 int, -- necessary
 @param2 int = 5 -- 5 as default
)

But as shree.pat18 said you need to call the optional function parameter with the "default". Like:

dbo.myFnc(5, default)
5im
  • 224
  • 2
  • 13
  • yes, but I have to rewrite whole project writing _default_... So without _default_ in UDF impossible? – procma Jul 24 '14 at 08:56
0

I am not sure which SQL you are referring to, but is it possible to overload the UDF? 

dbo.myFnc(Param1 int )

dbo.myFnc(Param1 int , Param2 int)

some sql allows it some doesn't. If yours doesn't, maybe you could try something the could handle null as input

create function func
(
    @in1 int,
    @in2 int
)
returns int
as
begin
    declare @count int

    select @count = count(*)
    from table
    where
        field1 = isnull(@in1, field1)
        and field2 = isnull(@in2, field2)

    return @count
end
go

so you could do something like

select func(null,9)
Jacky Cheng
  • 1,536
  • 1
  • 10
  • 22
  • Thanx for good kick ;) But my problem is, that one UDF is used through a big part of the project. Now we need to extend the UDF functionality (close to "overloading"). We cannot use same name for two different UDFs (_fnc(p1)_ and _fnc(p1,p2)_). It is not allowed. In the best way, we do not want to rewrite whole project by filling _default_ key word or something else. – procma Jul 24 '14 at 08:50
  • Exactly _fnc(p1)_ and _fnc(p2)_ cannot be declared separately, so I am looking for solution which creates one UDF woth optional params. So it looks impossible. Maybe rewrite it to SP...? – procma Jul 24 '14 at 08:53
  • will it be possible to use a wrapper approach? funcExtended(param1, param2) { @temp=select func(param1); select @result=result from table where field1=@temp and field2=param2; } – Jacky Cheng Jul 24 '14 at 08:58
  • sure, wrapper possible, but asked directly for "clear" optional param. Understanding as a param, that need not to be filled (also no default key word) – procma Jul 24 '14 at 09:02
  • i got nothing then XD good luck with your quest – Jacky Cheng Jul 24 '14 at 09:05
  • Thanx a lot, Jacky Cheng, for your idea – procma Jul 24 '14 at 09:06