Stored Function with Multiple Queries and Different Selected Columns

Question

I have series of queries based on a report type. For simplicity here is an example of what i'm trying to do:

If @Reporttype = '1'
Select lcustomerid, lname, fname
from customers
Where dtcreated > @startdate

Else if @Reporttype = '2'
Select barcode, lname, fname 
from employees
where dtcreated > @startdate

Else if @reporttype = '3'
Select thetime, lname, name, barcode, lcustomerid
from Customers
where dtcreated > @startdate

You'll notice that I run 3 separate queries, based on the report type being passed. You'll also notice I am returning different columns and the number of columns.

I'd like to make this a stored function, and return the columns I need based on the report type I pass. However, I know that since the number of columns, and the column names are different - that's not going to work as a stored function as I'd like it to.

The major problem here will be reporting this information - I don't want to have separate functions, because i'll have to maintain different reports for each report type.

Is there a way I can make this work?

Answer 1

You can use multi-statement function but you need to specify all columns which will be returned by 3 select statements. It seems it's impossible return multiple result sets.

User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets. https://msdn.microsoft.com/en-us/library/ms191320.aspx

This is one inconvenience but in report you can use only columns you need, others will be nulls.

CREATE FUNCTION MyFun
(
    @Reporttype int,
    @startdate datetime
)
RETURNS 
@Result TABLE 
(
    lcustomerid int, 
    lname nvarchar(50),
    fname nvarchar(50),
    barcode int,
    thetime datetime,
    name nvarchar(50)
)
AS
BEGIN
    If @Reporttype = '1'
        insert into @Result (lcustomerid, lname, fname)
        select lcustomerid, lname, fname
        from customers
        Where dtcreated > @startdate

    Else if @Reporttype = '2'
        insert into @Result (barcode, lname, fname)
        Select barcode, lname, fname 
        from employees
        where dtcreated > @startdate

    Else if @reporttype = '3'
        insert into @Result (thetime, lname, name, barcode, lcustomerid)
        Select thetime, lname, name, barcode, lcustomerid
        from customers
        where dtcreated > @startdate

    RETURN 
END

So, you can call function in this way

SELECT * FROM dbo.MyFun (1, getdate())