title: Returning a resultset from a stored procedure with a table value function or view
body: <p>I am using SQL Server 2000 and feeling stuck about how to do this:</p>
  
  <p>I have a stored procedure that returns a single resultset which is bound to a gridview in a .Net web app.</p>
  
  <p>Now I'd like to have a "table" so I can use it in an existing VB6 application like this: </p>
  
  <pre><code>SELECT * FROM myTable 
  </code></pre>
  
  <p>...where the schema of "myTable" is reflects the columns in the result set returned from this sproc.</p>
  
  <p>Here is a script that works:</p>
  
  <pre><code>CREATE TABLE #ResultSet (
      StateFIPS           CHAR(2)
      ,CountyFIPS         CHAR(3)
  ,StateName          VARCHAR(30)
  ,CountyName         VARCHAR(40)
  ,MostRecentData_P       VARCHAR(20)
  ,PData              VARCHAR(3)
  ,AvgNbrMtgPerMonthInLastYear_P  INT
  ,MostRecentData_R       VARCHAR(20)
  ,RData              VARCHAR(3)
  ,AvgNbrMtgPerMonthInLastYear_R  INT
  ,MostRecentData_FHA     VARCHAR(20)
  ,MostRecentData_VA      VARCHAR(20)
  )
  INSERT INTO #ResultSet
  EXECUTE dbo.FetchCoverageByState_V2 
  SELECT * FROM #ResultSet
  </code></pre>
  
  <p>I tried to put this into a view but I get slapped with errors: </p>
  
  <pre><code>"Views or functions are not allowed on temporary tables"
  </code></pre>
  
  <p>I also tried a function like this:</p>
  
  <pre><code>create function dbo.udfCoverages() 
  
  returns @ResultSet table (
      StateFIPS   CHAR(2)
      ,CountyFIPS CHAR(3)
  ,StateName  VARCHAR(30)
  ,CountyName VARCHAR(40)
  ,PData      VARCHAR(3)
  ,RData      VARCHAR(3)  
  ) as
  INSERT @ResultSet (
      StateFIPS           CHAR(2)
      ,CountyFIPS         CHAR(3)
  ,StateName          VARCHAR(30)
  ,CountyName         VARCHAR(40)
  ,MostRecentData_P       VARCHAR(20)
  ,PData              VARCHAR(3)
  ,AvgNbrMtgPerMonthInLastYear_P  INT
  ,MostRecentData_R       VARCHAR(20)
  ,RData              VARCHAR(3)
  ,AvgNbrMtgPerMonthInLastYear_R  INT
  ,MostRecentData_FHA     VARCHAR(20)
  ,MostRecentData_VA      VARCHAR(20)
  )
  EXECUTE dbo.FetchCoverageByState_V2  
  return
  </code></pre>
  
  <p>In the UDF attempt, I am getting syntax errors near the INSERT and I am wondering if SQL Server 2000 supports this. </p>
  
  <p>What would you recommend? </p>
  
  <p><strong><em>EDIT-UPDATE (per first suggestion from Ray below):</em></strong></p>
  
  <pre><code>create function dbo.udfCoverages() 
  returns @ResultSet table (
      StateFIPS   CHAR(2)
      ,CountyFIPS CHAR(3)
  ,StateName  VARCHAR(30)
  ,CountyName VARCHAR(40)
  ,PData      VARCHAR(3)
  ,RData      VARCHAR(3)  
  ) as
  BEGIN   
  INSERT @ResultSet 
  (
           StateFIPS          CHAR(2)
          ,CountyFIPS         CHAR(3)
      ,StateName          VARCHAR(30)
      ,CountyName         VARCHAR(40)
      ,MostRecentData_P       VARCHAR(20)
      ,PData              VARCHAR(3)
      ,AvgNbrMtgPerMonthInLastYear_P  INT
      ,MostRecentData_R       VARCHAR(20)
      ,RData              VARCHAR(3)
      ,AvgNbrMtgPerMonthInLastYear_R  INT
      ,MostRecentData_FHA     VARCHAR(20)
      ,MostRecentData_VA      VARCHAR(20)
  )
      EXECUTE dbo.FetchCoverageByState_V2 
      return
  END
  </code></pre>
  
  <p>Here I get: Line 19: Incorrect syntax near 'CHAR'. NOTE: Line 19 is 2 lines after the INSERT above.</p>