JOIN Based Upon Stored Procedure Output and View

[MomentSurfer]

Greetings everyone,

I have a stored procedure that processes data and creates a temporary table to store the results. I wish to make the results available to other stored procedures and/or views (if possible) for the purpose of joining my results with other tables/views.

I do not want to include the JOIN inside my stored procedure that creates the temporary table. This is because the data it procedures is fairly common and the JOINS that need to happen vary based upon the needs of the application. For this reason I'd prefer to "layer" my approach.

Can this be done? I'd appreciate any direction you can provide.

 

[MartinCroft]

You could always make the table a permenant object or a global temp table that way other objects could access it. I dotn completely follow what you are trying to achieve. Are you saying you want to create the population of the table from a stored procedure but join on the stored proc????

 

[MomentSurfer]

Thanks for your reply.

I would like to have the stored procedure that creates the temporary table to not perform the join operation. Instead, I would like a second stored procedure to call the first stored procedure and then JOIN at that point.

The rationale is there will be several different uses for the information contained in the temporary table. Because of this, it will be merged with all sorts of different data. This is why I'd prefer to do the join in a second layer (the second stored procedure) so the first layer is easily recyclable.

I would like to avoid "copying & pasting" the logic of the stored procedure that creates the temporary table into each of the other procedures because it's fairly advanced and I would prefer to avoid that form of duplication, if possible.

Appreciate your response. The global temporary table sounds interesting. I'll have to research more about it as I've never needed to work with it in the past.

Thanks again.

David

 

[MartinCroft]

If the table structure is the same, i.e it never changes then why not use a permenent table and just truncate the table each time you run the populate, the performance benifit would be better than creating a temp table and removing and a lot easier. If the stored procedure is creating the population JOIN's on the fly you might not get the performance benifits as the execution plans compiled will be not be relevent the next time it runs, thats if you are running this lots of times. Is the population dynamic i.e the code is changing that populations the table or is it static