ansaurus

Question

How to query on table returned by Stored procedure within a procedure.

Answer 1

+1 A:

I think that your question is similar to this one:

http://stackoverflow.com/questions/58940/access-to-result-sets-from-within-stored-procedures-transact-sql-sql-server

Paddy 2010-06-09 11:37:13

Answer 2

+3 A:

This is not possible.

The only way I can think of is to insert the results from the Stored Proc in to a temp table and query the temp table. This won't work in your scenario as you are returning 4 tables.

Could you not rewrite the query you are interested in as a function or view?

EDIT:

You would do something along the lines of this (only if the Stored Proc returns one table)

Create Proc dbo.usp_FetchUsers

as

begin

Select UserId, UserName
From Users

End
Go

Create Table #t
(
UserId int,
UserName varchar(50)
)

Insert Into #t
Exec dbo.usp_FetchUsers

Select * From #t
Where UserId = 4

drop table #t

Barry 2010-06-09 11:40:27

@Barry: I would have to do same operations for getting single table, that's not an issue. I will do that. plz provide eg for dealing with single table. i.e. Copy data into temp table from sp.

Shantanu Gupta 2010-06-09 11:44:08

I have updated my answer

Barry 2010-06-09 11:47:45

NOooo, I dont wanted to do this overhead of writing queries for create table, Is it the only option or some other way also possible.

Shantanu Gupta 2010-06-09 11:49:16

I'm afraid not - the only other option would be to use OpenRowset as suggested by Martin Smith.

Barry 2010-06-09 11:51:44

+1 for good way. not accepted because martins approach is more handy

Shantanu Gupta 2010-06-09 11:54:23

Answer 3

+2 A:

Edit This wouldn't be suitable for 4 tables but you say in the comments you can convert to return one.

You can use OPENROWSET for this (Doesn't appear to be mentioned in Paddy's linked question). This requires adhoc distributed queries enabled.

Example usage

SELECT  *
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl

Martin Smith 2010-06-09 11:42:06

@Martin: Which one would be better in terms of performance. The one suggested by you or using temp table.

Shantanu Gupta 2010-06-09 11:47:04

@Shantanu I don't know. Might be worth trying them both and looking at the statistics and execution plans.

Martin Smith 2010-06-09 11:48:37

Oh my goodness! that's really different. Nice trick to work

Shantanu Gupta 2010-06-09 11:52:59

+1 this is a nice approach. Although it does require `Ad Hoc Distributed Queries` to be enabled on the server.

Barry 2010-06-09 11:56:23

I +1'ed yours as well. It is less convenient but I suspect will give better execution plans if the stored proc returns many records and it needs to be joined onto other tables.

Martin Smith 2010-06-09 12:05:43

ansaurus

tags:

views:

answers:

How to query on table returned by Stored procedure within a procedure.

related questions