[tsql] T-SQL - function with default parameters

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?

You can call it three ways - with parameters, with DEFAULT and via EXECUTE

SET NOCOUNT ON;

DECLARE
@Table  SYSNAME = 'YourTable',
@Schema SYSNAME = 'dbo',
@Rows   INT;

SELECT dbo.TableRowCount( @Table, @Schema )

SELECT dbo.TableRowCount( @Table, DEFAULT )

EXECUTE @Rows = dbo.TableRowCount @Table

SELECT @Rows

One way around this problem is to use stored procedures with an output parameter.

exec sp_mysprocname @returnvalue output, @firstparam = 1, @secondparam=2

values you do not pass in default to the defaults set in the stored procedure itself. And you can get the results from your output variable.

With user defined functions, you have to declare every parameter, even if they have a default value.

The following would execute successfully:

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