How to query on table returned by Stored procedure within a procedure
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.
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
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
I think that your question is similar to this one:
Access to Result sets from within Stored procedures Transact-SQL SQL Server
加载中,请稍侯......
精彩评论