SQL SERVER 2016: Refactoring function to execute with default parameter

Question

I have the default_report function to fetch last 24 months report

CREATE FUNCTION dbo.default_report
RETURNS TABLE
AS
RETURN
(
    SELECT ID,sender,recipient,amount,currency
    FROM reports
    where submit_date >= dateadd(month,datediff(month,0,getdate())-24,0)
)

I have the custom_report function to fetch the report for specific dates

CREATE FUNCTION dbo.custom_report(@start_date DATE,@end_date DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT ID,sender,recipient,amount,currency
    FROM reports
    where submit_date BETWEEN @start_date AND @end_date
)

The only variable in the above two functions is the where clause, is there a way to combine the above two functions into one and make them work?

Gordon Linoff · Accepted Answer · 2020-01-21 22:03:52Z

1

Well, you can do:

CREATE FUNCTION dbo.custom_report (
    @start_date DATE = NULL,
    @end_date DATE = NULL
) RETURNS TABLE
AS
RETURN (SELECT ID, sender, recipient, amount, currency
        FROM reports
        WHERE submit_date >= COALESCE(@start_date, dateadd(month, -24, datefromparts(year(getdate()), month(getdate()), 1))) AND
              submit_date <= COALESCE(@end_date, getdate())
       );

I am guessing you have no future dates, so the condition on @end_date gets everything after the @start_date condition.

I also changed the calculation of the date 24 months ago so it is more intelligible.

edited Jan 21, 2020 at 22:03

answered Jan 21, 2020 at 18:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

OTUser Over a year ago

Yes I dont have any future dates, I had to change the @start_date DATE DEFAULT NULL to @start_date DATE its working otherwise am getting incorrect syntax near DEFAULT compiler error

OTUser Over a year ago

Also, quey works fine with NULL values SELECT top 10 * FROM dbo.custom_reports(NULL,NULL) but if I run with input SELECT top 10 * FROM dbo.custom_reports(1/1/2018,1/31/2018) am getting Msg 206, Level 16, State 2, Line 3 Operand type clash: int is incompatible with date

Gordon Linoff Over a year ago

@RanPaul. . . I always think the syntax for "default" parameters should use default rather than =. I fixed the answer. As for your second comment, dates should be surrounded by single quotes and in YYYY-MM-DD (or YYYYMMDD) format.

Collectives™ on Stack Overflow

SQL SERVER 2016: Refactoring function to execute with default parameter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related