By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

SQL Using a single variable to store multiple results

Plater
Expert 5K+
P: 7,872
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?
Oct 1 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Oct 1 '08 #2

Plater
Expert 5K+
P: 7,872
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).
Oct 1 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Oct 2 '08 #4

Plater
Expert 5K+
P: 7,872
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)
Oct 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.