Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Angate
Starting Member
24 Posts |
 Posted - 2009-11-30 : 13:09:11
|
| I am working on a .NET application that uses an SQL Server 2005 DB and will display a list of users that placed an order during a specified time frame. Grouped with each user in the list will be each of their order headers, and within each order header will be each of that order's line items. I have a customer table, an orders table that contains order header information, and an order lines table that contains each line item in their related order.My plan is to have a single stored procedure that accepts a date range, selects all orders placed in that date range in one return table, then selects the users that placed those orders in another return table, and selects all line items related to those orders in a third return table. This takes 3 select statements, the second and third statements will rely on the return value of the first. My question is how to use the results of the first select statement as the filter criteria for the second and third? I could simply repeat the first statement for a WHERE EXISTS, but it seems wasteful to run the same select query 3 times.Is there a way to do this? I am sorry if this has been asked before but I do not know the terminology for what I am asking that is required for an intelligent search.EDIT:I resolved this using the following article. I declare a table variable, insert into it with a select and then use it for my other select statements where in (select id from temp table)[url]http://www.sqlteam.com/article/temporary-tables[/url] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-30 : 13:13:49
|
| i think you should bring all the info as a single resultset and do your required filtering into sections at your .NET code |
 |
|
|
Angate
Starting Member
24 Posts |
Posted - 2009-11-30 : 21:56:47
|
| My concern their is that each line item in an order will also have the full contents of the order header and all of the user info attached. It will result in a massive redundancy of table data. If there is no other option that is the solution I will use, but it is not all that appealing. |
|
|
|
Angate
Starting Member
24 Posts |
Posted - 2009-12-04 : 23:39:51
|
| What I am trying to do is do my fist select statement into a temp table then do two other select statements that use a filter line of 'WHERE in IN (temp table)'. I can't figure this out.It is not an option to push them all into one table because of the stupid amount of redundant information that would have to show. to do that each line would contain all of hte user information, all of an orders header and one of it's order lines, and all of that would show for each of hundreds of order lines making the results hundreds of times larger than necessary. |
|
|
|
|
|
|
|
|
RESOLVED Multiple selects in stored procedure