MSSQL2000: Using a stored procedure results as a table in sql

Question

Let's say I have 'myStoredProcedure' that takes in an Id as a parameter, and returns a table of information.

Is it possible to write a SQL statement similar to this?

SELECT
    MyColumn
FROM
   Table-ify('myStoredProcedure ' + @MyId) AS [MyTable]

I get the feeling that it's not, but it would be very beneficial in a scenario I have with legacy code & linked server tables

Thanks!

Answer 1

You can use a table value function in this way.

Here is a few tricks...

Answer 2

No it is not - at least not in any official or documented way - unless you change your stored procedure to a TVF.

But however there are ways (read) hacks to do it. All of them basically involved a linked server and using OpenQuery - for example seehere. Do however note that it is quite fragile as you need to hardcode the name of the server - so it can be problematic if you have multiple sql server instances with different name.

Answer 3

Here is a pretty good summary of the ways of sharing data between stored procedures http://www.sommarskog.se/share_data.html.

Basically it depends what you want to do. The most common ways are creating the temporary table prior to calling the stored procedure and having it fill it, or having one permanent table that the stored procedure dumps the data into which also contains the process id.

Table Valued functions have been mentioned, but there are a number of restrictions when you create a function as opposed to a stored procedure, so they may or may not be right for you. The link provides a good guide to what is available.

SQL Server 2005 and SQL Server 2008 change the options a bit. SQL Server 2005+ make working with XML much easier. So XML can be passed as an output variable and pretty easily "shredded" into a table using the XML functions nodes and value. I believe SQL 2008 allows table variables to be passed into stored procedures (although read only). Since you cited SQL 2000 the 2005+ enhancements don't apply to you, but I mentioned them for completeness.

Most likely you'll go with a table valued function, or creating the temporary table prior to calling the stored procedure and then having it populate that.

Answer 4

While working on the project, I used the following to insert the results of xp_readerrorlog (afaik, returns a table) into a temporary table created ahead of time.

 INSERT INTO [tempdb].[dbo].[ErrorLogsTMP]
 EXEC master.dbo.xp_readerrorlog

From the temporary table, seelct the columns you want.