SQL Server 2008：将STORED PROCEDURE（动态列）的结果与SELECT语句的结果相结合

I have results that are generated by this stored procedure.

我有这个存储过程生成的结果。

I want to join these results with data in another table. I've seen various examples of doing this be creating a temporary table and inserting into it, however, this would not be ideal as the stored procedure returns many dynamic columns which are subject to change. Is there a way to join them dynamically?

我想将这些结果与另一个表中的数据结合起来。我已经看到这样做的各种示例是创建一个临时表并插入其中，但是，这不是理想的，因为存储过程会返回许多可能发生变化的动态列。有没有办法动态加入它们？

Example scenario:

示例场景：

Stored Procedure Returns This:

EXEC uspGetProductCategories

products_id | products_model | Leather Seats | Heated Seats | Tapedeck | Heater | Hybrid | Sunroof | Cruise Control
===================================================================================================================
100         | Saturn Vue     | N             | N            | Y        | N      | N      | N       | N
200         | Toyota Pruis   | Y             | N            | N        | Y      | Y      | N       | N
300         | Ford Focus     | N             | N            | N        | Y      | N      | N       | Y

I want to JOIN it with a SQL query that generates something like:

SELECT * FROM Products_Detail

products_id | manufacturer | purchaser | pay_type
=================================================
100         | GM           | GREG      | P
200         | TOYT         | SAM       | P
300         | FORD         | GREG      | L

In other words...

Is there a painless way to accomplish this? Here is some psedo code of what I'd like to achieve (though I'm aware that this doesn't work):

有没有一种无痛的方法来实现这一目标？这是我想要实现的一些psedo代码（虽然我知道这不起作用）：

SELECT pd.*, sp.* FROM Products_Detail pd
    LEFT JOIN uspGetProductCategories sp ON pd.product_id = sp.product_id

Again, I know you can't do this, but hopefully it describes the logic I'm looking for.

我再次知道你不能这样做，但希望它描述了我正在寻找的逻辑。

Example Desired Output

products_id | manufacturer | purchaser | pay_type | products_model | Leather Seats | Heated Seats | Tapedeck | Heater | Hybrid | Sunroof | Cruise Control
=========================================================================================================================================================
100         | GM           | GREG      | P        | Saturn Vue     | N             | N            | Y        | N      | N      | N       | N
200         | TOYT         | SAM       | P        | Toyota Pruis   | Y             | N            | N        | Y      | Y      | N       | N
300         | FORD         | GREG      | L        | Ford Focus     | N             | N            | N        | Y      | N      | N       | Y

4 个解决方案

#1

If you cannot create a temp table with the data from the dynamic stored procedure, why not just join on the table directly:

如果无法使用动态存储过程中的数据创建临时表，为什么不直接加入表：

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(categories_name) 
                    from Categories
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ',IsNull(' + QUOTENAME(categories_name)+', ''N'')'+' as '+QUOTENAME(categories_name) 
                    from Categories
                    group by categories_name, categories_id
                    order by categories_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
            select *
            from Products_Detail pd
            left join
            (
              SELECT products_id,
                    products_model,' + @colsNull + ' from 
               (
                  select p.products_id,
                    p.products_model,
                    c.categories_name,
                    ''Y'' flag
                  from products p
                  left join Products_Categories pc
                    on p.products_id = pc.products_id
                  left join Categories c
                    on pc.categories_id = c.categories_id
              ) x
              pivot 
              (
                  max(flag)
                  for categories_name in (' + @cols + ')
              ) p 
            ) p 
              on pd.products_id = p.products_id'

execute(@query)

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#2

Try using this ugly solution if no way to reorganize your code.

如果无法重新组织代码，请尝试使用这个丑陋的解决方案。

SELECT * INTO #tmp
FROM OPENROWSET('SQLNCLI', 'server=INSTANCENAME;database=DBNAME;trusted_connection=yes', 'uspGetProductCategories') A

You must have allowed Ad Hoc Distributed Queries in your server.

您必须在服务器中允许Ad Hoc Distributed Queries。

#3

-1

You can do this:

你可以这样做：

        INSERT aTemptable 
        EXEC yourstoredproc

if #t has been defined but you cannot do select into #t and have it be created dynamically.

如果已经定义了#t但你不能选择#t并动态创建它。

#4

-1

create proc sp_emp18
(
@cust_id int,@name varchar(20),@order_id int,@order_date date
)
as 
begin
select e.cust_id,e.name,d.order_id,d.order_date 
from 
customer e inner join orders d on e.cust_id=d.cust_id
end

exec sp_emp18 101,'ram',99,'2016-07-21'

#1