T-SQL - function with default parameters

Question

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?

Wickner

The answer that states, you MUST supply all parameters for user defined functions is the correct answer. AS of 2022, it doesn't matter if the function is given a default parameter or not, you MUST still supply one when calling it or use the DEFAULT keyword in order to get the benefit of the default value. – Shaving

Answer 1

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.

Bosch

Seeing this I am frustrated. I am not getting advantage of default concept here... I need to go and change all the places now. – Hyperon

@Lijo, you still get the advantage of not duplicating your concrete default value at each call. – Astrosphere

As we are not allowed to overlay, and the "default" thus has limited usability, often the best approach will be to create a new extended version with a suffix (say CheckIfSFExistsEX here) with the extra parameters, and changing the original function to just calling the extended version with the "default" parameter. This way ALL existing code works, and you only have one place to maintain. – Situate

Ugh Microsoft. It's not really an optional parameter if you are required to pass it a value. I hope they can change this in a future version. – Aliquant

@Aliquant Agreed, but in their defense, they didn't say it's an optional parameter. It's a parameter with a default value. It's only benefit is enabling the function caller to not have to have know what value to set the parameter to. I guess they felt it is important that the caller acknowledge the presence of the parameter - i.e. to opt out. But I too would prefer an optional parameter. – Adan

To add insult to injury, functions won't let you name your arguments either. So you either take a trip to the definition, or you sit there and wonder what 23 and default even are. This is rather puzzling, especially considering stored procedures (a much older spec) have always supported naming arguments. – Bobsledding

Answer 2

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

Fourgon

Why the DEFAULT keyword is required in select, but can be omitted in execute? This sucks :/ Hopefully this will be fixed someday. – Bengurion

@Misiu, it is not something that needs to be "fixed". That's by design. I read a lot of alternatives to approach an "ideal" goal of simply being able to call a function without specifying every argument, but I didn't see a clear explanation of why that is required. Code should be clear and one strategy to achieve that is requiring the coder to always be aware that "hey, you're calling a function that DOES have this and this other arguments, that happen to have default values. Don't forget that the default values MAY BE CHANGED". So, IMO, this is a good "bad thing" to have. – Iorgos

@GustavoPinsard One good reason to allow this is when you are adding a new parameter to function. If you don't have to specify the default value, the function can continue to work in existing applications. If you want to do that now, you need to create a new function with the extra parameter, and call it from the original (now deprecated) function. There is some value to that, but IMO it creates extra noise in the db. The new function usually ends up named worse than the original, since function overloading is not possible either. – Malang

Answer 3

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;

Stagemanage

This is the best answer. – Shaving

Answer 4

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.

Antonetta

Changing your function to a stored procedure is in general not a good solution, because a stored procedure cannot be called from within a query, but a function can. – Captain

True, however not all blocks of code need to be called from within a query. It has been shown that sql does not have a good method of handling default values for functions (using the default keyword is almost as much work as adding a value). It is not a good general solution, but it works great in certain use cases. – Antonetta

People keep marking this down, however I am sticking with it. If you have the need to have a reusable chunk of code, that will not be called inside of queries, and you want the flexibility of true optional parameters with default values, a stored procedure is still better than a function. – Antonetta

Using stored procedures does not mean using them instead of using a function - it can mean using the stored procedure as a wrapper around a function. I use this technique often; the default keywords are now hidden inside the proc. I think this idea is just fine. It also lets me make more complicated defaults if I want - separate from the function, which can be left in a more pure state. – Stammel