SQL Server: How to call table valued function without providing input parameter values

Question

I have a need to create a table valued function in SQL Server. The function has input parameters, but all of them have default values. If I want to SELECT from the function using default values for every input parameter, I cannot figure out how to call the function.

Below is the CREATE FUNCTION as well as my four attempts to call it without providing input-parameter values. All four attempts failed:

CREATE FUNCTION fx_my_table_valued_function
(@my_input_parameter AS VARCHAR(30) = 1)
RETURNS TABLE
RETURN SELECT 'x' AS my_column;

select * from fx_my_table_valued_function;
select * from fx_my_table_valued_function();
select * from [fx_my_table_valued_function];
select * from [fx_my_table_valued_function]();

Thanks in advance for any help.

You have to use the DEFAULT keyword. Which is not very useful. rextester.com/ILQTK99749. There is no possibility to omit the parameter entirely as in other languages. Or as for stored procedures. — Martin Smith
– Martin Smith, Commented Dec 22, 2017 at 21:44

Brien Foss · Accepted Answer · 2017-12-22 21:57:22Z

2

Try this:

CREATE FUNCTION fx_my_table_valued_function
(@my_input_parameter AS VARCHAR(30) = 1)
RETURNS @tbl TABLE (my_column VARCHAR(max))
AS
BEGIN
RETURN 
END

Then:

SELECT * FROM fx_my_table_valued_function(default)

SQL Fiddle

edited Dec 22, 2017 at 21:57

answered Dec 22, 2017 at 21:49

Brien Foss

3,3483 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server: How to call table valued function without providing input parameter values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related