UNION the results from two stored procedures

27 Jan 2006 5:23 AM

Scott A. Keen

I have two stored procedures which return results in the same column format.
I would like to have a third stored procedure which will union the results
of the two stored procedures, eliminate duplicates (distinct) and put the
results in order by userid.

pseudocode:

CREATE PROCEDURE [sp_unionresults]
(
@parm1    int,
@parm2    varchar(5),
@parm3    smalldatetime,
)
AS
SELECT DISTINCT
sp_firstresults @parm1, @parm2
UNION ALL
sp_secondresults @parm3
ORDER BY
userid

Can some help me with this? Thanks.

27 Jan 2006 6:16 AM

Jack Vamvas

One possible way of doing it is : Output the 2 sp results into a temp table
via a Cursor and then make the recordset available from the #temp table

--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp

Show quote

"Scott A. Keen" <nore***@scottkeen.com> wrote in message
news:uYIEVHwIGHA.1192@TK2MSFTNGP11.phx.gbl...
> I have two stored procedures which return results in the same column
format.
> I would like to have a third stored procedure which will union the results
> of the two stored procedures, eliminate duplicates (distinct) and put the
> results in order by userid.
>
> pseudocode:
>
> CREATE PROCEDURE [sp_unionresults]
> (
> @parm1    int,
> @parm2    varchar(5),
> @parm3    smalldatetime,
> )
> AS
> SELECT DISTINCT
>   sp_firstresults @parm1, @parm2
> UNION ALL
>   sp_secondresults @parm3
> ORDER BY
>   userid
>
> Can some help me with this? Thanks.
>
>

27 Jan 2006 1:52 PM

Scott Morris

Lookup the INSERT ... EXECUTE syntax in BOL - insert the resultsets into a
temp table and then select the final resultset from the temp table.

20 Feb 2006 1:56 PM

Andreas1974

> [...] eliminate duplicates (distinct) [...]

Just a comment regarding the DISTINCT combined with UNION. The ALL keyword
means that you do not want distinct records, since a distinct result is the
default behavious of a UNION.

From SQL BOL:
By default, the UNION operator removes duplicate rows from the result set.
If you use ALL, all rows are included in the results and duplicates are not
removed.

Andreas