Combining an executable string with the main sql in an sql stored procedure
Within my sql procedure I create an temporary table and then pivot it. This is dynamic so in the end I don't know how many columns there are. Lets make this table:
equipmentID | Name1 | Name2 | Name3 1 | 34.54 | 21.23 | 213.65 2 | 334 | 23.421 | 23.12 3 | 43.4 | 2.34 | 23.41
The query used to generate this is stored in a string and is executing by calling execute(@stringQuery).
My other table is fetched with a normal sql query and generates another set of information (including the equipmentID that is included in the pivot table.
What I want to do is join these two tables (based on the equipmentID so that the stored procedure returns a single table instead of two but I can't figure out how to do this.
So far I have tried several things:
Keep the main (second) query and add the execute line to in at the end of the FROM section so after all the tables are joined I have:
LEFT JOIN execute(@stringQuery) as strQ on strQ.equipmentID = mainQ.equipmentID
Made the second query a string so they are both fetched with the execute command OR created another stored procedure that contained the main sql so (again) they both use the execute command. This does fetch the information it just doesn't combine it. In this case I tried to combine these two executes with:
execute('select * from (' + @query1 + ') as tbl2 left join ' + @query2 + ' as tbl1 on tbl2.equipmentID= tbl1.equipmentID')
Convert the main query into a string and then combined them using a LEFT JOIN so it becomes one big query.
All of these gave me issues. I figured this was a common thing and wasn't sure how to approach solving it other than with what I tried already.
EDIT When I try to create a temporary table and the insert into (or select from) I get told the temporary table name is an invalid object.
How about have the dynamic query output to a temp table. Then you can just reference the output like any other table.
SELECT x,y,z INTO #MyTempTable ...
SELECT * FROM TABLE JOIN #MyTempTable ON ...
Your question is somewhat harder than it may seem. The trouble stems from the fact that you have a table with an unknown format (the dynamically generated columns).
The table with columns that only become known at runtime forces a solution that is either complex and cumbersome, or a solution that is not ideal.
The global temp table solution you have chosen may be one of the simplest solutions, but have a look here (skip down to "Global Temp Tables" or search for "##") for some pitfalls with your chosen solution: http://www.sommarskog.se/share_data.html
The short answer is that there is no good way to do it.
I got this working. What I did was use a global temporary table. That way I can use it in multiple execute statement rather than if I used a normal temporary table. This way I was also able to join on the global temporary table. Just keep in mind to use a unique id per procedure so that you can tell what rows you are currently working with as this can be seen over several procedures. This way I am also able to delete the rows (based on the id) at the end.