SQL Using a single variable to store multiple results

[Plater]

Ok so currently I have a store procedure that does roughly this:
[code=sql]
SELECT
IDX, FirstName, LastName, Email, Phone, etc, etc2, etc3
FROM
ABunchOfTables
WHERE
ALotOfLogic=tru e
[/code]

Due to the nature of what I need elsewhere, it would be good if it did this:
[code=sql]
SELECT
IDX, FirstName, LastName, Email, Phone, etc, etc2, etc3
FROM
ABunchOfTables
WHERE
ALotOfLogic=tru e
END
SELECT
IDX, LastName
FROM
ABunchOfTables
WHERE
ALotOfLogic=tru e
END
[/code]

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:
[code=sql]
SELECT
@IDX=IDX, FirstName, @LastName=LastN ame, Email, Phone, etc, etc2, etc3
FROM
ABunchOfTables
WHERE
ALotOfLogic=tru e
END
SELECT
@IDX, @LastName
END
[/code]

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:

SELECT 
   '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
FROM
   ABunchOfTables
WHERE
   ALotOfLogic=true
UNION ALL
SELECT 
   '2' as SortId, IDX, LastName, FirstName, Email, Phone, etc, etc2, etc3
FROM
   ABunchOfTables
WHERE
   ALotOfLogic=true
ORDER BY 1

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)