title: Return multiple tables from a T-SQL function in SQL Server 2008
body: <p>You can return a single table from a T-SQL Function in SQL Server 2008.</p>
  
  <p>I am wondering if it is possible to return more than one table. </p>
  
  <p>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.</p>
  
  <p>An simplified example of what this would look like with copy and paste:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So, the only difference between the two queries is the Table being filtered, and HOW (the Where clause).</p>
  
  <p>I would like to know if I could return Filter1 &amp; Filter2 from a function.  I am also open to suggestions on different ways to approach this problem.</p>