SQL Function Parameter default as select

Question

Function A, scalar, returns smallint (a year)
Function B, scalar, returns smallint (a month)
Function C, table, displays users based on year and month.

They work fine.

I would like to set default parameter of Function C to come from Func A & B like:

@year smallint = SELECT FunctA(), @month smallint = SELECT FunctB()

Is there anyway around this?

We are on Azure SQL Managed Instance.

I added the azure-sql-database tag. In the future, tagging your question accurately helps you because it attracts the attention of the people most able to give you good answers. — Bill Karwin
– Bill Karwin, Commented Dec 31, 2020 at 21:16

sticky bit · Accepted Answer · 2020-12-31 21:07:58Z

1

On thing you could use is something like

IF @year IS NULL
BEGIN
  SET @year = functa();
END;

IF @month IS NULL
BEGIN
  SET @month = functb();
END;

(or whatever the analog syntax is in the DBMS you didn't disclose).
And when you want the defaults, pass NULLs as parameters.

edited Dec 31, 2020 at 21:07

answered Dec 31, 2020 at 20:09

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

2 Comments

Amommy Over a year ago

Tried that. Not allowed in table function. Unless I am putting the 'IF' in the wrong place

GSerg Over a year ago

@Amommy It is allowed in a multistep table-valued function. It is not allowed in an inline table-valued function. If you have an inline table-valued function, you can use isnull(@year, functa()) everywhere where @year is expected, but that is likely to kill performance. You can play with various ways to work around that, such as outer apply (select isnull(@year, functa())) as actual_value(year), but it is not guaranteed to perform better.

Collectives™ on Stack Overflow

SQL Function Parameter default as select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related