|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Union with the results of two SPHi all,
I've been asked the question of one can peform a union with the results of two or more SP's Thanks Robert Robert Bravery wrote:
> Hi all, Assuming the recordset returned by the two procedures has the same > I've been asked the question of one can peform a union with the results of > two or more SP's > > Thanks > Robert > > > > structure, i.e. the same fields are returned, then you have a couple of options: 1. Insert the results of each sproc into seperate temp tables, and then query/union the two temp tables together: INSERT INTO #temp1 EXEC sproc1 INSERT INTO #temp2 EXEC sproc2 SELECT col1, col2, col3 FROM #temp1 UNION SELECT col1, col2, col3 FROM #temp2 2. Create a self-referencing linked server, and use OPENQUERY to execute the sprocs: SELECT col1, col2, col3 FROM OPENQUERY(servername, 'EXEC sproc1') UNION SELECT col1, col2, col3 FROM OPENQUERY(servername, 'EXEC sproc2') HI tracy and all,
Thanks for the quick response. I would guess that the same could work fro reporting services Thanks Robert Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:4509672E.90809@realsqlguy.com... > Robert Bravery wrote: > > Hi all, > > I've been asked the question of one can peform a union with the results of > > two or more SP's > > > > Thanks > > Robert > > > > > > > > > > Assuming the recordset returned by the two procedures has the same > structure, i.e. the same fields are returned, then you have a couple of > options: > > 1. Insert the results of each sproc into seperate temp tables, and then > query/union the two temp tables together: > > INSERT INTO #temp1 > EXEC sproc1 > > INSERT INTO #temp2 > EXEC sproc2 > > SELECT col1, col2, col3 > FROM #temp1 > UNION > SELECT col1, col2, col3 > FROM #temp2 > > > 2. Create a self-referencing linked server, and use OPENQUERY to > execute the sprocs: > > SELECT col1, col2, col3 > FROM OPENQUERY(servername, 'EXEC sproc1') > UNION > SELECT col1, col2, col3 > FROM OPENQUERY(servername, 'EXEC sproc2') > > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > I would guess that the same could work fro reporting services You might need to add SET FMTONLY ON at the beginning of the TSQL batch for RS to be able to extract ,meta-data about the result set. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Robert Bravery" <m*@u.com> wrote in message news:%23pgwOtA2GHA.4976@TK2MSFTNGP02.phx.gbl... > HI tracy and all, > > Thanks for the quick response. > I would guess that the same could work fro reporting services > > Thanks > Robert > > "Tracy McKibben" <tr***@realsqlguy.com> wrote in message > news:4509672E.90809@realsqlguy.com... >> Robert Bravery wrote: >> > Hi all, >> > I've been asked the question of one can peform a union with the results > of >> > two or more SP's >> > >> > Thanks >> > Robert >> > >> > >> > >> > >> >> Assuming the recordset returned by the two procedures has the same >> structure, i.e. the same fields are returned, then you have a couple of >> options: >> >> 1. Insert the results of each sproc into seperate temp tables, and then >> query/union the two temp tables together: >> >> INSERT INTO #temp1 >> EXEC sproc1 >> >> INSERT INTO #temp2 >> EXEC sproc2 >> >> SELECT col1, col2, col3 >> FROM #temp1 >> UNION >> SELECT col1, col2, col3 >> FROM #temp2 >> >> >> 2. Create a self-referencing linked server, and use OPENQUERY to >> execute the sprocs: >> >> SELECT col1, col2, col3 >> FROM OPENQUERY(servername, 'EXEC sproc1') >> UNION >> SELECT col1, col2, col3 >> FROM OPENQUERY(servername, 'EXEC sproc2') >> >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com > > That should have been SET FMTONLY OFF of course...
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Robert Bravery" <m*@u.com> wrote in message news:%23pgwOtA2GHA.4976@TK2MSFTNGP02.phx.gbl... > HI tracy and all, > > Thanks for the quick response. > I would guess that the same could work fro reporting services > > Thanks > Robert > > "Tracy McKibben" <tr***@realsqlguy.com> wrote in message > news:4509672E.90809@realsqlguy.com... >> Robert Bravery wrote: >> > Hi all, >> > I've been asked the question of one can peform a union with the results > of >> > two or more SP's >> > >> > Thanks >> > Robert >> > >> > >> > >> > >> >> Assuming the recordset returned by the two procedures has the same >> structure, i.e. the same fields are returned, then you have a couple of >> options: >> >> 1. Insert the results of each sproc into seperate temp tables, and then >> query/union the two temp tables together: >> >> INSERT INTO #temp1 >> EXEC sproc1 >> >> INSERT INTO #temp2 >> EXEC sproc2 >> >> SELECT col1, col2, col3 >> FROM #temp1 >> UNION >> SELECT col1, col2, col3 >> FROM #temp2 >> >> >> 2. Create a self-referencing linked server, and use OPENQUERY to >> execute the sprocs: >> >> SELECT col1, col2, col3 >> FROM OPENQUERY(servername, 'EXEC sproc1') >> UNION >> SELECT col1, col2, col3 >> FROM OPENQUERY(servername, 'EXEC sproc2') >> >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com > > Not directly... But there are (not so neat) workarounds:
INSERT INTO t1 (c1, c2, ...) EXEC p1 INSERT INTO t2 (c1, c2, ...) EXEC p2 SELECT ... FROM t1 UNION SELECT ... FROM t1 SELECT ... FROM OPENROWSET(... 'p1') UNION SELECT ... FROM OPENROWSET(... 'p2') Also see OPENQUERY, -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Robert Bravery" <m*@u.com> wrote in message news:%23Zj1faA2GHA.4976@TK2MSFTNGP02.phx.gbl... > Hi all, > I've been asked the question of one can peform a union with the results of > two or more SP's > > Thanks > Robert > > > > You will need to execute both procs into a temp table first and then UNION
them after. You could also insert both results into 1 temp table and select straight from it without the UNION. Immy Show quote "Robert Bravery" <m*@u.com> wrote in message news:%23Zj1faA2GHA.4976@TK2MSFTNGP02.phx.gbl... > Hi all, > I've been asked the question of one can peform a union with the results of > two or more SP's > > Thanks > Robert > > > >  |
|||||||||||||||||||||||
Other interesting topics