Ok so currently I have a store procedure that does roughly this: -
SELECT
-
IDX, FirstName, LastName, Email, Phone, etc, etc2, etc3
-
FROM
-
ABunchOfTables
-
WHERE
-
ALotOfLogic=true
-
Due to the nature of what I need elsewhere, it would be good if it did this: -
SELECT
-
IDX, FirstName, LastName, Email, Phone, etc, etc2, etc3
-
FROM
-
ABunchOfTables
-
WHERE
-
ALotOfLogic=true
-
END
-
SELECT
-
IDX, LastName
-
FROM
-
ABunchOfTables
-
WHERE
-
ALotOfLogic=true
-
END
-
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: -
SELECT
-
@IDX=IDX, FirstName, @LastName=LastName, Email, Phone, etc, etc2, etc3
-
FROM
-
ABunchOfTables
-
WHERE
-
ALotOfLogic=true
-
END
-
SELECT
-
@IDX, @LastName
-
END
-
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?
4 6402
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
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).
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
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: soup_or_power |
last post by:
Can someone please explain why the following code breaks
when there is a single drop-down list, otherwise works fine
with multiple drop-down lists?
Many thanks
function xyz() {
var...
|
by: typingcat |
last post by:
First of all, I'm an Asian and I need to input Japanese, Korean and so
on. I've tried many PHP IDEs today, but almost non of them supported
Unicode (UTF-8) file.
I've found that the only Unicode...
|
by: Rudolf Bargholz |
last post by:
Hi ,
I have the following tables
-------------
PAX:
Id
Order_Id
Name
Position
|
by: Kamilche |
last post by:
I have looked at many object-oriented programming frameworks out there
for C. Though the ideas presented are intriguing, and I've used some
of them in my own work, they all suffered some drawback...
|
by: Lokkju |
last post by:
I am pretty much lost here - I am trying to create a managed c++
wrapper for this dll, so that I can use it from c#/vb.net, however, it
does not conform to any standard style of coding I have seen....
|
by: Keith G. Murphy |
last post by:
I'm trying to get a feel for what most people are doing or consider best
practice.
Given a mod_perl application talking to a PostgreSQL database on the
same host, where different users are...
|
by: serge |
last post by:
Is it generally or almost always better to have multiple
small SPs and functions to return a result set instead of
using a single big 1000+ lines SP?
I have one SP for example that is 1000+...
|
by: Pieter |
last post by:
Hi,
I'm having some troubles with my numeric-types in my VB.NET 2005
application, together with a SQL Server 2000.
- I first used Single in my application, and Decimal in my database. But a...
|
by: greek_bill |
last post by:
Hi,
I'm interested in developing an application that needs to run on more
than one operating system. Naturally, a lot of the code will be shared
between the various OSs, with OS specific...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |