0

I've created a stored procedure that creates a temp table. Now I need to be able to query over it using .NET. So I would need something like:

SELECT * FROM storedProcedure() WHERE ...

The table created by the stored procedure is dynamic as well so the schema will change. Using SQL Server 2005.

I cant use a function because what needs to be returned is a dynamic table. Having to define the table beforehand makes a function useless.

I ended up creating a global temp table with the help of dynamic SQL I was able to create a dynamic global temp table and access it via .NET.

user1938919
  • 53
  • 9
  • 1
    see this post: [http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure][1] [1]: http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure – BWS Jul 29 '13 at 14:37
  • 1
    Shall we assume SQL Server is your RDBMS? How about a global temp table? – Bridge Jul 29 '13 at 14:37
  • @Bridge I was thinking about global temp table but would that not be overwritten if there are multiple transactions going on at the same time? – user1938919 Jul 29 '13 at 14:40
  • @Bridge I ended up using a global temp table as a function cant return a dynamic table it was no help. Thanks for the suggestion. – user1938919 Jul 30 '13 at 14:33

2 Answers2

1

As the reference indicates, you use something like:

insert into @t(<columns here>)
    exec stored_procedure;

However, in many cases, you can replace such a stored procedure with a user defined function. These are defined to return values, and can be used exactly as you want to use it:

select *
from dbo.udf_MyFunction();

There are, of course, advantages and disadvantages to each approach. One disadvantage to the stored procedure method is that you cannot nest such stored procedures. One disadvantage to the function approach is that you cannot use dynamic SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Using a function then is it possible to return a dynamic table because as I have seen with functions you must declare the returned table and its schema on creation? – user1938919 Jul 29 '13 at 15:05
  • @user1938919 . . . With an inline table valued function, the return clause specifies the format. For a multi-line table valued function, then you need to declare the type as part of the definition. However, to capture the stored procedure results in a table, you need to define the table *before* doing the insert, so you also need the table definition there. – Gordon Linoff Jul 29 '13 at 15:15
  • Not going to work then I need to be able to create a dynamic table out of a JSON, join it with another table and then return that to be queried. Thanks though. – user1938919 Jul 29 '13 at 15:50
0

Actually, you can select from stored procedure like this

select * 
from openrowset('SQLNCLI', 'Server=<your server here>;Database=<your database here>;Trusted_Connection=yes;','set fmtonly off exec <your procedure here>')

I, however, couldn't recommend doing this in production, think about changing your stored procedure to user defined function

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197