Optional parameters in SQL UDF without the DEFAULT keyword

Question

I am looking for a solution on how to create SQL UDF with optional parameters.

Pseudo-code for a function where Param1 is needed and Param2 can be populated (but not needed):

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

Is there any way to create a similar function? For an existing embedded pattern, observe the STR function

STR ( float_expression [ , length [ , decimal ] ] )

Answer 1

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

--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 [ ) ]
[ ; ]

MSDN source

So you can do something like:

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

But since shree.pat18 said you need to call an optional function parameter with a value of "default". How:

dbo.myFnc(5, default)

Answer 2

, . default, , , .

, :

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

...

:

dbo.myFnc(Param1, default)

Answer 3

, SQL , UDF?

dbo.myFnc(Param1 int )

dbo.myFnc(Param1 int , Param2 int)

sql . , , -, null

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

-

select func(null,9)