How to both return and store result of select in SQL Server?

Question

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.

Could you show us what u have tried,Contents of the database,your expected output. — Raging Bull
– Raging Bull, Commented Feb 20, 2014 at 5: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
– Danny Guo, Commented Feb 20, 2014 at 6: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: stackoverflow.com/questions/2199942/… This lets you stitch together a self referencing table in one query. It may or may not be more efficient. — Nick.Mc
– Nick.Mc, Commented Feb 20, 2014 at 6: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
– Danny Guo, Commented Feb 20, 2014 at 6:49
OUTPUT parameters is perhaps what you require..technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx — Mudassir Hasan
– Mudassir Hasan, Commented Feb 20, 2014 at 6:55

Abhishek Chaudhary · Accepted Answer · 2014-02-20 06:06:13Z

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.

answered Feb 20, 2014 at 6:06

Abhishek Chaudhary

8077 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to both return and store result of select in SQL Server?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related