Making a table from a resultset from a sProc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Making a table from a resultset from a sProc

Author

Topic

jjamjatra
Starting Member

13 Posts

Posted - 2010-01-12 : 14:23:23

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" 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 -- this is the sproc
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
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

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

What would you recommend?

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 15:36:03

[code]
INSERT @ResultSet
(
StateFIPS ~~CHAR(2)~~
,CountyFIPS ~~CHAR(3)~~
...
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources