Alter a SQL Server Function to Accept New Optional Parameter

Alter a SQL server function to accept new optional parameter

From CREATE FUNCTION:

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

So you need to do:

SELECT dbo.fCalculateEstimateDate(647,DEFAULT)

Optional parameters in SQL UDF without DEFAULT keyword

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)

T-SQL - function with default parameters

you have to call it like this

SELECT dbo.CheckIfSFExists(23, default)

From Technet:

When a parameter of the function has a default value, the keyword
DEFAULT must be specified when the function is called in order to
retrieve the default value. This behaviour is different from using
parameters with default values in stored procedures in which omitting
the parameter also implies the default value. An exception to this
behaviour is when invoking a scalar function by using the EXECUTE
statement. When using EXECUTE, the DEFAULT keyword is not required.

Infinite optional parameters

For a set of items, you could consider passing a table of values to your function?

Pass table as parameter into sql server UDF

See also http://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

To answer your question directly, no, there is no equivalent to the params keyword. The approach I'd use is the one above - Create a user-defined table type, populate that one row per value, and pass that to your scalar function to operate on.

EDIT: If you want to avoid table parameters, and are on SQL 2012, look at the CONCAT function:

http://technet.microsoft.com/en-us/library/hh231515.aspx

CONCAT ( string_value1, string_value2 [, string_valueN ] )

This is only for the built-in CONCAT function, you couldn't roll-your-own function with "params" style declaration.

optional parameters in SQL Server stored proc?

You can declare like this

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* check for the NULL / default value (indicating nothing was passed */
if (@Parameter3 IS NULL)
BEGIN
    /* whatever code you desire for a missing parameter*/
    INSERT INTO ........
END

/* and use it in the query as so*/
SELECT *
FROM Table
WHERE Column = @Parameter

Optional parameter in SQL server

If you don't want to go adjusting all of your existing stored procedures that reference the function then I think you would need to create a new function with the code from your existing one

CREATE FUNCTION CalculateAverageForUser2
(
    @userid int,
    @param2 nvarchar(10) = NULL
)
RETURNS float
AS
/*Code from existing function goes here*/

Then just change the existing function to the following

ALTER FUNCTION CalculateAverageForUser 
(
 @userid int
)
RETURNS float
AS
BEGIN
RETURN dbo.CalculateAverageForUser2(@userid, DEFAULT)
END

Passing default values of parameters to Table Valued Functions

You can't omit the parameters when you call a function. This isn't anything you're doing wrong in the syntax, it's just simply not supported by SQL Server. Stored procedures have optional parameters, but functions do not.

You can, however, supply default:

SELECT code FROM dbo.Xtest(default, default);

Or in this case if you know the defaults are NULL you can do:

SELECT code FROM dbo.Xtest(NULL, NULL);

Also please always use the schema prefix (in this case dbo.) when creating and referencing any object, especially functions.

---

Related Topics