T-SQL - function with default parameters

tsql function default-parameters

nirmus · Dec 2, 2011 · Viewed 157.8k times · Source

I have this script:

CREATE FUNCTION dbo.CheckIfSFExists(@param1 INT, @param2 BIT = 1 )
RETURNS BIT
AS
BEGIN
    IF EXISTS ( bla bla bla )
        RETURN 1;
    RETURN 0;
END
GO

I want to use it in a procedure in this way:

IF dbo.CheckIfSFExists( 23 ) = 0
    SET @retValue = 'bla bla bla';

But I get the error:

An insufficient number of arguments were supplied for the procedure or function dbo.CheckIfSFExists.

Why does it not work?

Answer

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.

T-SQL - function with default parameters

Answer

Related questions