[Plater]

Ok so currently I have a store procedure that does roughly this:

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    IDX, FirstName, LastName, Email, Phone, etc, etc2, etc3
3. FROM
4.    ABunchOfTables
5. WHERE
6.    ALotOfLogic=true
7.  

Due to the nature of what I need elsewhere, it would be good if it did this:

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    IDX, FirstName, LastName, Email, Phone, etc, etc2, etc3
3. FROM
4.    ABunchOfTables
5. WHERE
6.    ALotOfLogic=true
7. END
8. SELECT 
9.    IDX, LastName
10. FROM
11.    ABunchOfTables
12. WHERE
13.    ALotOfLogic=true
14. END
15.  

Now that is a waste, both because the columns returned in the 2nd query are a subset of the first query AND because a lot of logic has to happen and database hits to produce a same results (although with less columns)as well.

I would like to be able to do something more like this:

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    @IDX=IDX, FirstName, @LastName=LastName, Email, Phone, etc, etc2, etc3
3. FROM
4.    ABunchOfTables
5. WHERE
6.    ALotOfLogic=true
7. END
8. SELECT 
9.    @IDX, @LastName
10. END
11.  

Except that I need it to return multiple rows for each query.


Now I don't think this is possible, but I have been surprized before.
I think what will end up happening is I just have the first query in the storedproc, and I call the proc twice?

[ck9663]

Sounds like a job for a CURSOR.

What are you going to do with the values? Are you going to run a t-sql and pass it as a parameter?

-- CK

[Plater]

Well what I have been doing (I'm doing this in .NET) is just running the first query. The result of the query is stored in a DataTable object, all nice and simple like.
BUT, to get what I want to mimic the 2nd query, I loop through the results and "manually" copying over the two columns I want into another DataTable object.
Would be nice if the stored procedure would return two result sets, which .NET would just turn into 2 DataTable object auto-magically.

Its a really dumb reason I need them that way. I suppose technically the 2nd query would be more like SELECT DISTINCT.

The first query returns me all the data.
The second query would return me every unique last name from withen all the data. I actually think I might be able to run a 2ndary query from withen .NET itself on the resultset (DataTable object).

[ck9663]

I'll give it a shot.........

I'll do this query:

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    '1' as SoriID, IDX, LastName, space(x)  as FirstName, space(x) as Email, space(x) as Phone, spacae(x) as etc, space(x) as etc2, space(x) as etc3
3. FROM
4.    ABunchOfTables
5. WHERE
6.    ALotOfLogic=true
7. UNION ALL
8. SELECT 
9.    '2' as SortId, IDX, LastName, FirstName, Email, Phone, etc, etc2, etc3
10. FROM
11.    ABunchOfTables
12. WHERE
13.    ALotOfLogic=true
14. ORDER BY 1
15.  

Then If your table object can be indexed, you can search SortId =1 then WHILE SortId = 1 loop will be your second query. If you need the second part, search SortId = 2 then do a WHILE SortId = 2 loop for your second query. You're going to run the two query twice any way. But you'll save time from reconnecting to your DB twice and having two dataobects.

Did I make sense? Hope this helps.

-- CK

[Plater]

Thanks for the help, unfortunatly it turns out I had to run the single query twice.
Turns out I need the results of the "second" query to dictate parameters in the first.
(The 2nd query provided a list of unique names from the large data set, the user can select one of those names and only return the values from the large data set with that name in it (among other things)