Stored procedures and UNION (Help!!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedures and UNION (Help!!)

Author

Topic

PeteMcBraida
Starting Member

3 Posts

Posted - 2002-12-16 : 11:23:16

I have a small problem trying to write a bit of SQL.
I want to use UNION to combine results from a select statement with results from a stored procedure. I tried writing something like:

DECLARE @myParameter int
@myParameter =1
SELECT a,b,c FROM myTable
UNION
EXECUTE myProcedure @myParameter
(Where the procedure returns the same columns as the select statement)

It totally doesn't like it.
is it possible to do unions with stored procedures??? if yes then how?

Any help would be greatly appreciated!!

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-12-16 : 11:42:27

sure, but you have to use temp tables... it would be better to just combine the select statements ... and if you do use the procedure you'll have to make sure you know what columns (names/datatypes) it returns...


CREATE TABLE #results (a INT, b INT, c INT)

INSERT INTO #results (a, b, c)
 EXECU myProcedure @myParameter

SELECT a,b,c FROM myTable UNION SELECT a,b,c FROM #results

DROP TABLE #results

PeteMcBraida
Starting Member

3 Posts

Posted - 2002-12-16 : 11:50:44

The reason I would rather keep the stored proc is that I use it lots in different places. Therefore if I have to change it I would rather only have to change it in one place. Using a temp table seems a bit drastic though, I wish there was a better way!!!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-16 : 12:02:47

Can you manifest the procs rowset with a view?

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-16 : 12:05:44

This is, to me, the #1 problem with stored procedures.

However -- if you are using SQL 2000, you can use a User-Defined Function. they work GREAT for this purpose and you can SELECT from them like any other table or view.

So you would have:

DECLARE @myParameter int
@myParameter =1
SELECT a,b,c FROM myTable
UNION
SELECT * FROM myFunction(@myParameter )

Look at Books On-line for how to create a function that accept parameters and returns a table.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-12-16 : 12:28:26

The trouble is that SPs can change the database. If you were allowed to use the result of an SP in a SELECT then additional semantics would have to be imposed on the operation of the SELECT.
It's like the difference between a pure function and an IO operation in a modern functional language (e.g. Haskell): you just cannot be allowed to do I/O in the middle of a function!

PeteMcBraida
Starting Member

3 Posts

Posted - 2002-12-16 : 12:34:32

I have implemented the functionality using a User Defined function.

It works a treat.

Cheers People!!!!

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-18 : 21:38:02

sqlTEAM
three approaches plus
an investigative comment

this place has a parquet floor <80's CELTICS reference>

Voted best SQL forum nickname...."Tutorial-D"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-19 : 08:24:25

quote:

sqlTEAM
three approaches plus
an investigative comment

this place has a parquet floor <80's CELTICS reference>

Voted best SQL forum nickname...."Tutorial-D"

Nice comment!! (Look at where I live on my profile!)

BT could learn alot from this thread.

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-19 : 09:31:51

quote:
(Look at where I live on my profile!)

back at you,

NBA's Greatest Moments
Bird Picks Pistons' Pockets

Bird followed up his Game 5 steal with 37 points, 9 rebounds and 9 assists in Game 7.

The defending champion Boston Celtics were down and almost out.
Playing the young, tough-as-nails Detroit Pistons in the 1987 Eastern Conference Finals,
the aging Celtics were in danger of losing Game 5, which would have given the Pistons a
chance to clinch the series at home in Game 6.
With Boston down by a point and Detroit in possession of the ball in the closing seconds
of the game, those famed Celtic leprechauns decided to make an appearance.
As Detroit's Isiah Thomas prepared to toss the ball inbounds from the sideline,
Boston's Larry Bird looked away from his man and stole a glance at Thomas.
He saw the Pistons' captain look toward center Bill Laimbeer in the low post
an instant before releasing the ball. So Bird cut into the passing lane and stole
the ball before it could reach Laimbeer's hands. His momentum looked like it would
carry him out of bounds, but Bird somehow managed to gather his balance at the
baseline and turn toward the court, where he spotted teammate Dennis Johnson
beginning his cut from the foul line toward the basket. Bird whipped a crisp pass to DJ
who laid it in with one second remaining for a 108-107 victory.
The steal was remarkable. Bird's instinct and ability to turn it into the winning basket
only compounded the greatness of the play.
"Larry's mind takes an instant picture of the whole court," noted Bill Fitch, Bird's
first coach with the Celtics. "He sees creative possibilities."
The Celtics went on to win the series in seven games and advance to the NBA Finals
for the fourth year in a row, where they would surrender their title to the Los Angeles Lakers
in six games.

However this was the final humiliation for both
Laimbeer and Thomas. It spurned the final piece of the
puzzle needed by the Bad Boys. Total team concept, the
schooling the Celtics had dished out for so many years.

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources