如何查询由过程中的Stored过程返回的表 [英] How to query on table returned by Stored procedure within a procedure
问题描述
我有一个执行一些ddl dml操作的存储过程。
在处理来自CTE和交叉应用和其他复杂事情的数据后，它检索数据。
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.
现在这返回一个4个表，绑定到各种源在前端。
现在我想使用表中的一个进一步处理，以便从中获得更有用的信息。
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.
例如。这个表将包含大约2000记录，其中大多数我想获得属于住宿的记录。
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
我想要这样的东西
select description from exec spName where fk_category_id=5
请记住，这个spName返回我4个表，我想对其索引将知道我的一个表上执行一些查询。
我使用的是Sql Server 2008，但想要一个兼容的2005年的解决方案。
I am using Sql Server 2008 but would like a compatible solution for 2005 also.
推荐答案
编辑这不适合4个表，但你在评论中说可以转换为返回。
Edit This wouldn't be suitable for 4 tables but you say in the comments you can convert to return one.
您可以使用 OPENROWSET 这个（似乎没有提到在Paddy的链接题）。
You can use OPENROWSET for this (Doesn't appear to be mentioned in Paddy's linked question). This requires adhoc distributed queries enabled.
使用示例
SELECT *
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl
这篇关于如何查询由过程中的Stored过程返回的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
