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

Question

I have a stored procedure that is performing some ddl dml operations. It retrieves a data after processing data from CTE and cross apply and other such complex things.

Now this returns me a 4 tables which gets binded to various sources at frontend. Now I want to use one of the table to further processing so as to get more usefull information from it.

eg. This table would be containing approx 2000 records at most of which i want to get records that belongs to lodging only.

PK_CATEGORY_ID       DESCRIPTION                                        FK_CATEGORY_ID       IMMEDIATE_PARENT                                   Department_ID        Department_Name                                    DESCRIPTION_HIERARCHY                                                                                                                                                                                                                                            DEPTH       IS_ACTIVE   ID_PATH                                                                                                                                                                                                                                                          DESC_PATH
-------------------- -------------------------------------------------- -------------------- -------------------------------------------------- -------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                    Food                                               NULL                 NULL                                               1                    Food                                               (Food) Food                                                                                                                                                                                                                                                      0           1           0                                                                                                                                                                                                                                                                Food
5                    Chinese                                            1                    Food                                               1                    Food                                               (Food) ----Chinese                                                                                                                                                                                                                                               1           1           1                                                                                                                                                                                                                                                                Food->Chinese
14                   X                                                  5                    Chinese                                            1                    Food                                               (Food) --------X                                                                                                                                                                                                                                                 2           1           1->5                                                                                                                                                                                                                                                             Food->Chinese->X
15                   Y                                                  5                    Chinese                                            1                    Food                                               (Food) --------Y                                                                                                                                                                                                                                                 2           1           1->5                                                                                                                                                                                                                                                             Food->Chinese->Y
65                   asdasd                                             5                    Chinese                                            1                    Food                                               (Food) --------asdasd                                                                                                                                                                                                                                            2           1           1->5                                                                                                                                                                                                                                                             Food->Chinese->asdasd
66                   asdas                                              5                    Chinese                                            1                    Food                                               (Food) --------asdas                                                                                                                                                                                                                                             2           1           1->5                                                                                                                                                                                                                                                             Food->Chinese->asdas
8                    Italian                                            1                    Food                                               1                    Food                                               (Food) ----Italian                                                                                                                                                                                                                                               1           1           1                                                                                                                                                                                                                                                                Food->Italian
48                   hfghfgh                                            1                    Food                                               1                    Food                                               (Food) ----hfghfgh                                                                                                                                                                                                                                               1           1           1                                                                                                                                                                                                                                                                Food->hfghfgh
55                   Asd                                                1                    Food                                               1                    Food                                               (Food) ----Asd                                                                                                                                                                                                                                                   1           1           1                                                                                                                                                                                                                                                                Food->Asd
2                    Lodging                                            NULL                 NULL                                               2                    Lodging                                            (Lodging) Lodging                                                                                                                                                                                                                                                0           1           0                                                                                                                                                                                                                                                                Lodging
3                    Room                                               2                    Lodging                                            2                    Lodging                                            (Lodging) ----Room                                                                                                                                                                                                                                               1           1           2                                                                                                                                                                                                                                                                Lodging->Room
4                    Floor                                              3                    Room                                               2                    Lodging                                            (Lodging) --------Floor                                                                                                                                                                                                                                          2           1           2->3                                                                                                                                                                                                                                                             Lodging->Room->Floor
9                    First                                              4                    Floor                                              2                    Lodging                                            (Lodging) ------------First                                                                                                                                                                                                                                      3           1           2->3->4                                                                                                                                                                                                                                                          Lodging->Room->Floor->First
10                   Second                                             4                    Floor                                              2                    Lodging                                            (Lodging) ------------Second                                                                                                                                                                                                                                     3           1           2->3->4                                                                                                                                                                                                                                                          Lodging->Room->Floor->Second
11                   Third                                              4                    Floor                                              2                    Lodging                                            (Lodging) ------------Third                                                                                                                                                                                                                                      3           1           2->3->4                                                                                                                                                                                                                                                          Lodging->Room->Floor->Third
29                   Fourth                                             4                    Floor                                              2                    Lodging                                            (Lodging) ------------Fourth                                                                                                                                                                                                                                     3           1           2->3->4                                                                                                                                                                                                                                                          Lodging->Room->Floor->Fourth
12                   Air Conditioned                                    3                    Room                                               2                    Lodging                                            (Lodging) --------Air Conditioned                                                                                                                                                                                                                                2           1           2->3                                                                                                                                                                                                                                                             Lodging->Room->Air Conditioned
20                   With Balcony                                       12                   Air Conditioned                                    2                    Lodging                                            (Lodging) ------------With Balcony                                                                                                                                                                                                                               3           1           2->3->12                                                                                                                                                                                                                                                         Lodging->Room->Air Conditioned->With Balcony
24                   Mountain View                                      20                   With Balcony                                       2                    Lodging                                            (Lodging) ----------------Mountain View                                                                                                                                                                                                                          4           1           2->3->12->20                                                                                                                                                                                                                                                     Lodging->Room->Air Conditioned->With Balcony->Mountain View
25                   Ocean View                                         20                   With Balcony                                       2                    Lodging                                            (Lodging) ----------------Ocean View                                                                                                                                                                                                                             4           1           2->3->12->20                                                                                                                                                                                                                                                     Lodging->Room->Air Conditioned->With Balcony->Ocean View
26                   Garden View                                        20                   With Balcony                                       2                    Lodging                                            (Lodging) ----------------Garden View                                                                                                                                                                                                                            4           1           2->3->12->20                                                                                                                                                                                                                                                     Lodging->Room->Air Conditioned->With Balcony->Garden View
52                   Smoking                                            20                   With Balcony                                       2                    Lodging                                            (Lodging) ----------------Smoking                                                                                                                                                                                                                                4           1           2->3->12->20                                                                                                                                                                                                                                                     Lodging->Room->Air Conditioned->With Balcony->Smoking
21                   Without Balcony                                    12                   Air Conditioned                                    2                    Lodging                                            (Lodging) ------------Without Balcony                                                                                                                                                                                                                            3           1           2->3->12                                                                                                                                                                                                                                                         Lodging->Room->Air Conditioned->Without Balcony
13                   Non Air Conditioned                                3                    Room                                               2                    Lodging                                            (Lodging) --------Non Air Conditioned                                                                                                                                                                                                                            2           1           2->3                                                                                                                                                                                                                                                             Lodging->Room->Non Air Conditioned
22                   With Balcony                                       13                   Non Air Conditioned                                2                    Lodging                                            (Lodging) ------------With Balcony                                                                                                                                                                                                                               3           1           2->3->13                                                                                                                                                                                                                                                         Lodging->Room->Non Air Conditioned->With Balcony
71                   EA                                                 3                    Room                                               2                    Lodging                                            (Lodging) --------EA                                                                                                                                                                                                                                             2           1           2->3                                                                                                                                                                                                                                                             Lodging->Room->EA
50                   Casabellas                                         2                    Lodging                                            2                    Lodging                                            (Lodging) ----Casabellas                                                                                                                                                                                                                                         1           1           2                                                                                                                                                                                                                                                                Lodging->Casabellas
51                   North Beach                                        50                   Casabellas                                         2                    Lodging                                            (Lodging) --------North Beach                                                                                                                                                                                                                                    2           1           2->50                                                                                                                                                                                                                                                            Lodging->Casabellas->North Beach
40                   Fooding                                            NULL                 NULL                                               40                   Fooding                                            (Fooding) Fooding                                                                                                                                                                                                                                                0           1           0                                                                                                                                                                                                                                                                Fooding
41                   Pizza                                              40                   Fooding                                            40                   Fooding                                            (Fooding) ----Pizza                                                                                                                                                                                                                                              1           1           40                                                                                                                                                                                                                                                               Fooding->Pizza
45                   Onion                                              41                   Pizza                                              40                   Fooding                                            (Fooding) --------Onion                                                                                                                                                                                                                                          2           1           40->41                                                                                                                                                                                                                                                           Fooding->Pizza->Onion
47                   Extra Cheeze                                       41                   Pizza                                              40                   Fooding                                            (Fooding) --------Extra Cheeze                                                                                                                                                                                                                                   2           1           40->41                                                                                                                                                                                                                                                           Fooding->Pizza->Extra Cheeze
77                   Burger                                             40                   Fooding                                            40                   Fooding                                            (Fooding) ----Burger                                                                                                                                                                                                                                             1           1           40                                                                                                                                                                                                                                                               Fooding->Burger

This result is being obtained to me using some stored procedure which contains some DML operations as well.

i want something like this

select description from exec spName where fk_category_id=5

Remember that this spName is returning me 4 tables of which i want to perform some query on one of the table whose index will be known to me. I dont have to send it to UI before querying further.

I am using Sql Server 2008 but would like a compatible solution for 2005 also.

I dont think you can put select statement for SPs. better use table valued function inside the sp and do your exta bit of operation on the table returned by the function. — Chinjoo, Jun 09 '10 at 11:38
@Chinjoo: Could you please provide me an example considering that spName procedure returns me single table say above table. — Shantanu Gupta, Jun 09 '10 at 11:41
Could be done in the lines of : Create Proc spName ( @Parameter dataType ) AS Select * into #temptab from myFunction() Here Function would be something like this: Create Function myFunction() returns @temp table(id INT) as begin insert @temp select * from your table return end — Chinjoo, Jun 09 '10 at 12:40
@Chinjoo: I have heard that functions do not support dml operations. Then how it is possible. According to that this is not possible the solution you are providing. Moreover "Insert @temp select * from your table" is what i want but here table is being returned to me via stored procedure. I dont have a table at this point. — Shantanu Gupta, Jun 09 '10 at 14:01

codingbadger · Answer 1 · 2010-06-09T11:47:27.433

3

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

edited Jun 09 '10 at 11:47

answered Jun 09 '10 at 11:40

codingbadger

42,678
13
95
110

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 Jun 09 '10 at 11:44
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 Jun 09 '10 at 11:49
I'm afraid not - the only other option would be to use OpenRowset as suggested by Martin Smith. – codingbadger Jun 09 '10 at 11:51
+1 for good way. not accepted because martins approach is more handy – Shantanu Gupta Jun 09 '10 at 11:54

Martin Smith · Accepted Answer · 2010-06-09T11:47:12.377

3

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

edited Jun 09 '10 at 11:47

answered Jun 09 '10 at 11:42

Martin Smith

438,706
87
741
845

Which one would be better in terms of performance. The one suggested by you or using temp table. – Shantanu Gupta Jun 09 '10 at 11:47
@Shantanu I don't know. Might be worth trying them both and looking at the statistics and execution plans. – Martin Smith Jun 09 '10 at 11:48
+1 this is a nice approach. Although it does require `Ad Hoc Distributed Queries` to be enabled on the server. – codingbadger Jun 09 '10 at 11:56
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 Jun 09 '10 at 12:05

score 1 · Answer 3 · edited May 23 '17 at 10:32

1

I think that your question is similar to this one:

Access to Result sets from within Stored procedures Transact-SQL SQL Server

edited May 23 '17 at 10:32

Community

1
1

answered Jun 09 '10 at 11:37

Paddy

33,309
15
79
114

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

3 Answers3

Linked