SQL Function as Default Parameter Value

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.

SQL function as default parameter value?

Default value for stored procedures parameter have to be constants.
You'd need to do the following...

ALTER Procedure [dbo].[my_sp]
@currentDate datetime = null
AS
IF @currentDate is null
SET @currentDate = getdate()

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)

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.

Stored procedure with default parameters

I wrote with parameters that are predefined

They are not "predefined" logically, somewhere inside your code. But as arguments of SP they have no default values and are required. To avoid passing those params explicitly you have to define default values in SP definition:

Alter Procedure [Test]
    @StartDate AS varchar(6) = NULL, 
    @EndDate AS varchar(6) = NULL
AS
...

NULLs or empty strings or something more sensible - up to you. It does not matter since you are overwriting values of those arguments in the first lines of SP.

Now you can call it without passing any arguments e.g.
exec dbo.TEST

Why can't GETDATE() be used as the default value of a procedure parameter or a value in an EXECUTE statement?

This is covered in the documentation, CREATE PROCEDURE (Transact-SQL), under the default subheading in the arguments section:

A default value for a parameter. If a default value is defined for a
parameter, the procedure can be executed without specifying a value
for that parameter. The default value must be a constant or it can be
NULL. The constant value can be in the form of a wildcard, making it
possible to use the LIKE keyword when passing the parameter into the
procedure.

Emphasis mine.

GETDATE() is not a constant, so cannot be used an a DEFAULT value. Hence why you need to use the format below, as then the value of GETDATE() is determined at run time:

CREATE PROC YourProc @Param date = NULL
AS

    IF @Param IS NULL BEGIN
        SET @Param = GETDATE();
    END;
    ...

---

Related Topics