Hello, all

MSSQL allows default values in parameters to functions:

CREATE FUNCTION test( @v INT = 1 )
RETURNS TABLE AS
RETURN ( SELECT @v AS v )

but will not let me use them -- the invocation

SELECT * FROM test()

fails with:

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

The documentation says it should work:

-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ ,...n ]
]
)

The value of each declared parameter must be supplied by
the user when the function is executed, unless a default
for the parameter is defined.

What am I doing wrong on my MSSQL v. 13?

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:

MSSQL allows default values in parameters to functions:

CREATE FUNCTION test( @v INT = 1 )
RETURNS TABLE AS
RETURN ( SELECT @v AS v )

but will not let me use them -- the invocation

SELECT * FROM test()

fails with:

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

I'm about to say that I don't even want to talk about this, because
it is so stupid. That is, you must supply the DEFAULT keyword explicitly:

SELECT * FROM dbo.test(DEFAULT)

Which of course defies quite much of the idea with a default parameter
when you want to add a new parameter to an existing function.

Here is a feedback item you can vote for: https://feedback.azure.com/d365community/idea/95dbf609-5a25-ec11-b6e6- 000d3a4f0da0

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Erland Sommarskog:

I'm about to say that I don't even want to talk about
this, because it is so stupid. That is, you must supply
the DEFAULT keyword explicitly:

SELECT * FROM dbo.test(DEFAULT)

This is just... terrible.

Which of course defies quite much of the idea with a
default parameter when you want to add a new parameter to
an existing function.

Here is a feedback item you can vote for: https://feedback.azure.com/d365community/idea/95dbf609-5a25-ec11-b6e6-000d3a4f0da0

Thanks, I have voted and added a comment. I wonder why MS
have decided to deny me the basic politeness of showing my
name, and to disaply "Community user" instead...

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Anton Shepelev:

Thanks, I have voted and added a comment. I wonder why MS
have decided to deny me the basic politeness of showing my
name, and to disaply "Community user" instead...

Fixed.

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)