|
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?
|
|
Making a table from a resultset from a sProc