Last ResultSet

[chandru_244]

HI,
Consider the following procedure

Create Proc Testing
as
Select * from Temp1
Select * from Temp2

one will have 2 result sets,When this procedure is executed from another procedure.
But i need only the resultset of second query ie Select * from Temp2

Is there a way out.

Tx in advance.

Rgds,
Chandru

[Krzemo]

Create Proc Testing
as
Select * from Temp1
UNION ALL
Select * from Temp2

But Temp1 and Temp2 should have compatibile structure. ...

[womalley]

Select * usually is not a good idea if you can you should call out the columns that you need. This will help with defining the structure that Krzemo is talking about.

Consider this:

CREATE PROCEDURE spTesting
AS
--
SELECT Col1
,Col2
,Col3
FROM (SELECT MyFirstColumn As Col1
,MySecondColumn As Col2
,MyThirdColumn As Col3
,'TABLE 1' As ResultFrom
FROM Temp1

UNION ALL

SELECT MyFirstColumn As Col1
,MySecondColumn As Col2
,MyThirdColumn As Col3
,'TABLE 2' As ResultFrom
FROM Temp2) AS S1 -- yes this requires an alis

WHERE ResultFrom = 'TABLE 2'


----------------------------------------------------------
Well it may not be perfect but it will get you going in the right dirrection..

Hope this helps

Will

[exterminator]

Chandru, you code as is will not work. And suppose you get the syntaxes correct - you should get what you want from the stored procedure. You have not used a UNION or a UNION ALL and because of that you will just get the result of the last SELECT that appears in the procedure.

UNION/UNION ALL work only on queries that return similar typed column results (similar would also include implicit promotion from certain datatype to certain other) and same number of columns. Remove the * as mentioned by womalley.. they are worth the ease they provide in long term.. atleast in stored procedures.. you never know when you changed a column..added/dropped a column but forgot to make modifications to the code using the result from a select *. Regards.

[womalley]

To chandru_244:
I was just wondering if you were able to find a solution to you problem and if you did could you post the code for reference so others with the same question can see how you answered it?


Will

[exterminator]

I was just wondering if you were able to find a solution to you problem and if you did could you post the code for reference so others with the same question can see how you answered it?The problem is pretty simple ... I mean what's the problem if one needs only one recordset? Have a boolean passed as an input parameter to the stored procedure depending upon what he want - a union or just the resultset from second query..

Alternatively he could have 3 stored procs.. one to get first resultset.. one to get second resultset.. and one that internally calls both of the previous 2. It all depends on how one wants to implement this.. Just a thought... ;) Regards.