Plater 7,872
Recognized Expert Expert
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 6409 ck9663 2,878
Recognized Expert Specialist
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 7,872
Recognized Expert Expert
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 2,878
Recognized Expert Specialist
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 7,872
Recognized Expert Expert
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 f=document.forms;
for(var i=0; i < f.elements.length; i++)
|
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 support IDEs are DreamWeaver 8 and
Zend PHP Studio.
DreamWeaver provides full support for Unicode. However, DreamWeaver is
a web editor rather than a PHP IDE. It only supports basic IntelliSense
(or code completion) and doesn't have anything...
|
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 or another.
I have created a new method of doing OOP with C, something that fits
my needs particularly well. I haven't encountered anything else like
it, so I'm tossing it out to the Internet for comment.
...
|
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. It is
almost like it is trying to implement it's own COM interfaces...
below is the header, and a link to the dll+code:
Zip file with header, example, and DLL:...
| |
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 logging onto the web server using
LDAP for authentication, do most people
1) have the web server connecting to the database using its own user
account (possibly through ident), and controlling access to different
database entities strictly through...
|
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+ lines and early
analysis of the SP I see it first has 3 big blocks of code
separated by IF statements. Then within each IF block
of code I see 3-4 UNIONs. UNIONs that means
they are all returning the same columns so I am
guessing these are prime...
|
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
Single with value 4.475 was converted to a Decimal with value
4.4749999999999996D. So after inserting and selecting it from the database I
got another value than the original!
|
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 functionality being kept
separate.
I've been going over the various approaches I could follow in order to
implement the OS-specific functionality. The requirements I have are
as follows :
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |