0

I am doing a select within a SQL Server stored procedure. This select returns the results to the caller, which is what I need, but I also need to use one of the fields from the select in the rest of the stored procedure.

Right now, I am doing the select as normal. Then I create a temporary table, and re-do the same select again, but I insert the results into the temporary table. Is there a better way to do this?

For context, I am essentially pulling a tree out of a database. There are many one-to-many tables, and I am using the ids that I get from one level to pull out the next level.

Danny Guo
  • 892
  • 1
  • 14
  • 28
  • Could you show us what u have tried,Contents of the database,your expected output. – Raging Bull Feb 20 '14 at 05:59
  • My output is fine. It just seems like there should be a more efficient method since I am essentially doing every select twice. As an example, there is a "Continents" table, a "Countries" table, and a "Provinces" table. I need to pull out all the Countries that are in Asia and South America and all the Provinces that are in those Countries. There is a unique id between each Continent and each Country. There is also a unique id between each Country and each Province. – Danny Guo Feb 20 '14 at 06:01
  • Not sure if I fully understand (i.e. do you have a fixed number of levels) but have you looked into a CTE for this? Here is one o many examples: http://stackoverflow.com/questions/2199942/getting-hierarchy-data-from-self-referencing-tables This lets you stitch together a self referencing table in one query. It may or may not be more efficient. – Nick.Mc Feb 20 '14 at 06:28
  • I did see that question, but my situation is different because I do not have a self-referencing table. Rather, I have three different tables. The second table stores a one-to-many relationship between entities of Type A and entities of Type B. The third table stores a one-to-many relationship between entities of Type B and entities of Type C. – Danny Guo Feb 20 '14 at 06:49
  • OUTPUT parameters is perhaps what you require..http://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx – Mudassir Hasan Feb 20 '14 at 06:55

1 Answers1

1

You are selecting your data twice from the base tables (once for returning to user and once for inserting into temp table) and once from the temp table (to use the column ahead in the stored procedure).

If you selection query from base table is complex, what you can do is that select the data only once from your base table into temp table and then use the temp table twice (once to return the data to user and once to use the column).

This will reduce the execution time as the complex query would be executed only once and other selects are restricted to simple select from temp table.

Abhishek Chaudhary
  • 767
  • 6
  • 6