Return multiple tables from a T-SQL function in SQL Server 2008

Question

You can return a single table from a T-SQL Function in SQL Server 2008.

I am wondering if it is possible to return more than one table.

The scenario is that I have three queries that filter 3 different tables. Each table is filtered against 5 filter tables that I would like to return from a function; rather than copy and paste their creation in each query.

An simplified example of what this would look like with copy and paste:

FUNCTION GetValuesA(@SomeParameter int) RETURNS @ids TABLE (ID int) AS 

  WITH Filter1 As ( Select id FROM FilterTable1 WHERE Attribute=SomeParameter )
     , Filter2 As ( Select id FROM FilterTable2 WHERE Attribute=SomeParameter )

  INSERT INTO @IDs
  SELECT ID FROM ValueTableA
  WHERE ColA IN (SELECT id FROM Filter1)
  AND   ColB IN (SELECT id FROM Filter2)

  RETURN

-----------------------------------------------------------------------------

FUNCTION GetValuesB(@SomeParameter int) RETURNS @ids TABLE (ID int) AS 

  WITH Filter1 As ( Select id FROM FilterTable1 WHERE Attribute=SomeParameter )
     , Filter2 As ( Select id FROM FilterTable2 WHERE Attribute=SomeParameter )

  INSERT INTO @IDs
  SELECT ID FROM ValueTableB
  WHERE ColA IN (SELECT id FROM Filter1)
  AND   ColB IN (SELECT id FROM Filter2)
  AND   ColC IN (SELECT id FROM Filter2)

  RETURN

So, the only difference between the two queries is the Table being filtered, and HOW (the Where clause).

I would like to know if I could return Filter1 & Filter2 from a function. I am also open to suggestions on different ways to approach this problem.

You can't return two different result sets from the same procedure. If you're going to handle the the results the same from both queries consider a UNION, INTERSECT, or MINUS. — William Stearns, Nov 23 '11 at 20:02
@WilliamStearns "You can't return two different result sets from the same procedure" - that is a very incorrect statement: **you can**. SQL Server has always supported returning multiple result-sets from a `PROCEDURE`: [the documentation mentions this](https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure): "If multiple such SELECT statements are run during the execution of the stored procedure, multiple result sets will be sent to the client." and [people ask about it all the time](https://stackoverflow.com/q/40013747/159145). — Dai, Mar 05 '22 at 16:48

score 4 · Accepted Answer · answered Nov 23 '11 at 19:59

4

No.

Conceptually, how would you expect to handle a function that returned a variable number of tables? You would JOIN on two tables at once? What if the returned fields don't line up?

Is there some reason you can't have a TVF for each filter?

answered Nov 23 '11 at 19:59

JNK

63,321
15
122
138

Conceptually, I suppose I would want to be able to index each table in the results. @Results[0], @Results[1], @Results[2]. Though... this doesn't really exist in TSQL, so it's a wash. However, having a TVF for each filter like you mentioned I think may be the most appropriate solution. Thanks! – Sheldon Warkentin Nov 23 '11 at 21:18
1

@SheldonWarkentin - referencing by index implies you will have the function listed more than once. To me that means you will be running it more than once as well, which is a whole other issue. – JNK Nov 23 '11 at 21:20

score 2 · Answer 2 · answered Nov 23 '11 at 21:24

As others say, NO. A function in TSQL must return exactly one result (although that result can come in the form of a table with numerous values).

There are a couple of ways you could achieve something similar though. A stored procedure can execute multiple select statements and deliver the results up to whatever called it, whether that be an application layer or something like SSMS. Many libraries require you to add additional commands to access more result sets though. For instance, in Pyodbc to access result sets after the first one you need to call cursor.nextset()

Also, inside a function you could UNION several result sets together although that would require each result set to have the same columns. One way to achieve that if they have a different column structure is to add in nulls for the missing columns for each select statement. If you needed to know which select statement returned the value, you could also add a column which indicated that. This should work with your simplified example since in each case it is just returning a single ID column, but it could get awkward very quickly if the column names or types are radically different.

Return multiple tables from a T-SQL function in SQL Server 2008

2 Answers2