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.

It is a stored procedure that can get by with no parms passed to it. It returns a result set as I said which is currently used by a web app. — John Adams, Jan 11 '10 at 21:26

score 1 · Answer 1 · answered Jan 11 '10 at 21:07

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.

score 1 · Accepted Answer · edited May 23 '17 at 12:19

1

Try using OPENROWSET, as in this question. I thought I had blogged about it some time, but perhaps not.

Insert results of a stored procedure into a temporary table

Remember to upvote Aaron's answer.

edited May 23 '17 at 12:19

Community

1
1

answered Jan 11 '10 at 21:22

Rob Farley

15,625
5
44
58

Thanks Rob. Can you help with this syntax I've tried from Aaron's example: SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=(local)\johna;Trusted_Connection=yes;', 'EXECUTE dbo.FetchCoverageByState_V2') I get error: "SQL Server does not exist or access denied". All the examples seem to be for linked remote servers or for MS Access. Thanks. – John Adams Jan 12 '10 at 23:58
I don't have access to my scripts this week (I'm on my phone only). But there's an example in my scripts at http://msmvps.com/blogs/robfarley/archive/2008/09/03/dat283.aspx – Rob Farley Jan 13 '10 at 04:57
It might be the brackets. Try using localhost instead? Is johna the name of the instance? – Rob Farley Jan 13 '10 at 04:59
Your script example was helpful. I removed johna (which was my userid) and followed your syntax. I got a new error "Could not find stored procedure dbo.FetchCoverageByState_V2". Then I realized I had to expand to a 3part name so I prefixed dbo with the name of the database and it worked. You are great! Thanks for the help. I think I read somewhere that this technique would not be needed any longer if I could upgrade to SQL2005. This works for now. Thanks so much. – John Adams Jan 13 '10 at 18:03
If you want to use the results of a stored procedure as a table expression, you'll still need to do it this way in later versions of SQL. – Rob Farley Jan 13 '10 at 22:13

score 0 · Answer 3 · answered Jan 11 '10 at 21:06

0

you need begin and end wrapped around the function body (after the as)

answered Jan 11 '10 at 21:06

Ray

21,485
5
48
64

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

3 Answers3