Multiple Selects in SP
-
Hi I've got a login Proc that returns some data upon successful login. I've just added some checking code before this data to verify that the user may log in etc, but the resultsets from these checks are being returned in the dataset. Is there some way to clear the results before doing my final select statement? Thanks Russell
-
Hi I've got a login Proc that returns some data upon successful login. I've just added some checking code before this data to verify that the user may log in etc, but the resultsets from these checks are being returned in the dataset. Is there some way to clear the results before doing my final select statement? Thanks Russell
Not too sure if this will help but when I have a structure like that I use a reader. I can then write the following: if(r.Read()){ // they are valid, get user info if(r.NextResultSet()){ while(r.Read()){ // return additional data for that person } } } r.close(); *->>Always working on my game, teach me *->>something new. cout << "dav1d\n"; -- modified at 12:45 Monday 3rd October, 2005
-
Not too sure if this will help but when I have a structure like that I use a reader. I can then write the following: if(r.Read()){ // they are valid, get user info if(r.NextResultSet()){ while(r.Read()){ // return additional data for that person } } } r.close(); *->>Always working on my game, teach me *->>something new. cout << "dav1d\n"; -- modified at 12:45 Monday 3rd October, 2005
I'm trying to get SQL to not return the recordsets that the SP uses internally and just return the data i really want. There's quite a bit of branching in the checking code which means i don't know exactly how many select statements will be executed prior to the select that i want executed.
-
I'm trying to get SQL to not return the recordsets that the SP uses internally and just return the data i really want. There's quite a bit of branching in the checking code which means i don't know exactly how many select statements will be executed prior to the select that i want executed.
The easiest way around this is to not execute queries that return rows. Instead of:
SELECT * FROM Users WHERE ID = "Tom"Do:DELCARE @i INTEGER SELECT @i = COUNT(*) FROM Users WHERE ID = "Tom"You can use logic to determine if @i > 0 then process the rest of the login. You can use any number of variables in the SELECT. Just make sure your query only returns 1 row. You can also use EXISTS:IF EXISTS(SELECT * FROM Users WHERE ID = "Tom") BEGIN -- Do your work here END