Returning a resultset from a stored procedure with a table value function or view

Question

I am using SQL Server 2000 and feeling stuck about how to do this:

I have a stored procedure that returns a single resultset which is bound to a gridview in a .Net web app.

Now I'd like to have a "table" so I can use it in an existing VB6 application like this:

SELECT * FROM myTable 

...where the schema of "myTable" is reflects the columns in the result set returned from this sproc.

Here is a script that works:

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

I tried to put this into a view but I get slapped with errors:

"Views or functions are not allowed on temporary tables"

I also tried a function like this:

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

In the UDF attempt, I am getting syntax errors near the INSERT and I am wondering if SQL Server 2000 supports this.

What would you recommend?

EDIT-UPDATE (per first suggestion from Ray below):

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

Here I get: Line 19: Incorrect syntax near 'CHAR'. NOTE: Line 19 is 2 lines after the INSERT above.

Answer 1

No matter what you do, you will not be able to transform a procedure into a table valued function nor into a view. There are severe restriction around what a function is allowed to do and what not. Because a TVF or a view can be combined with any other statement, like appear in a SELECT as a subquery, or be part of an UPDATE/INSERT/DELETE and so on and so forth, there are certain behavior restrictions imposed on functions, specially when in comes to execution side effects. Procedure son the other hand are free as a bird.

The only thing you can do is to capture the output of a procedure into a table, using INSERT ... EXEC... and that's it, with the extra added restrictions that there cannot be another INSERT ... EXEC ... nested.

Eeverything you posted points that fetchCcoverageByState_V2 must be a table valued function, and that is the only avenue you should pursue.