1

I have a stored procedure that returns a dataset from a dynamic pivot query (meaning the pivot columns aren't know until run-time because they are driven by data).

The first column in this dataset is a product id. I want to join that product id with another product table that has all sorts of other columns that were created at design time.

So, I have a normal table with a product id column and I have a "dynamic" dataset that also has a product id column that I get from calling a stored procedure. How can I inner join those 2?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user441521
  • 6,942
  • 23
  • 88
  • 160
  • 5
    Create a Temp Table from the SP Output as shown [here](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table#29040625) and join your table with temp table as usual – techspider Sep 07 '16 at 19:29
  • As I won't know the schema the only way is a way that could have sever security risks? – user441521 Sep 07 '16 at 20:01
  • there are obvious risks; you may have to weigh your solution vs requirement; There could be a different approach altogether avoiding dynamic pivot output joining a table – techspider Sep 07 '16 at 20:02
  • 1
    Possible duplicate of [How can I join on a stored procedure?](http://stackoverflow.com/questions/921190/how-can-i-join-on-a-stored-procedure) – Vadzim Feb 22 '17 at 16:16

1 Answers1

0

Dynamic SQL is very powerfull, but has some severe draw backs. One of them is exactly this: You cannot use its result in ad-hoc-SQL.

The only way to get the result of a SP into a table is, to create a table with a fitting schema and use the INSERT INTO NewTbl EXEC... syntax...

But there are other possibilities:

1) Use SELECT ... INTO ... FROM

Within your SP, when the dynamic SQL is executed, you could add INTO NewTbl to your select:

SELECT Col1, Col2, [...] INTO NewTbl FROM ...

This will create a table with the fitting schema automatically.

You might even hand in the name of the new table as a paramter - as it is dynamic SQL, but in this case it will be more difficult to handle the join outside (must be dynamic again).

If you need your SP to return the result, you just add SELECT * FROM NewTbl. This will return the same resultset as before.

Outside your SP you can join this table as any normal table...

BUT, there is a big BUT - ups - this sounds nasty somehow - This will fail, if the tabel exists...

So you have to drop it first, which can lead into deep troubles, if this is a multi-user application with possible concurrencies.

If not: Use IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='NewTbl') DROP TABLE NewTbl;

If yes: Create the table with a name you pass in as parameter and do you external query dynamically with this name.

After this you can re-create this table using the SELECT ... INTO syntax...

2) Use XML

One advantage of XML is the fact, that any structure and any amount of data can be stuffed into one single column.

Let your SP return a table with one single XML column. You can - as you know the schema now - create a table and use INSERT INTO XmlTable EXEC ....

Knowing, that there will be a ProductID-element you can extract this value and create a 2-column-derived-table with the ID and the depending XML. This is easy to join.

Using wildcards in XQuery makes it possible to query XML data without knowing all the details...

3) This was my favourite: Don't use dynamic queries...

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • 1
    Thanks for the options. I'm a business first kind of person and I never like to limit business or requirements because technology may not quite be there to solve the business problem so option 3 isn't my favorite. I like to push boundaries and I do know that can come with issues. :) I see this as a technology issue that SQL Server doesn't have a great solution for but very much could make possible. – user441521 Sep 09 '16 at 14:19