Question about stored procedure and Union All

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

General SQL Server Forums

New to SQL Server Programming

Question about stored procedure and Union All

Author

Topic

The Fire Snake
Starting Member

2 Posts

Posted - 2012-10-04 : 13:04:33

Hi Everybody,
This is my first post on this forum and I am very new to TSQL. I do have some database experience, as I have created/modified SQL and PLSQL in Oracle. I am currently looking into debugging a SQL Server stored procedure that someone else wrote and I am confused. Here is the code paraphrased a bit:


CREATE PROCEDURE [dbo].[sp_MySP]
@p1 int OUTPUT,
@p2 int OUTPUT
AS
BEGIN

SELECT @p1 = count(*)
FROM <some table>
WHERE
(
   <Some conditions>
)

UNION ALL

SELECT @p2 = count(*)
FROM <same table>
WHERE
(
   <Different conditions>
)

END

GO

I think I understand what the above SP is doing for the most part. It has 2 output parameters and it assigns them the result of counts on the table. Then these 2 paraemters(with the count values) are returned back to the calling program. When I go to comple this code, I get an error saying something to the effect that you can't do an assignment before and after a UNION. So, I looked this up and was in the process of rewriting this with the UNION ALL, but in a different way to get over this error.

Then I started to looking into what is it actually doing and the UNION ALL being used here baffles me. I understand what a UNION ALL does, but how is it applicable here? It makes no sense why you would want to union 2 assignments together? Do you know what is going on with that? I was thinking I was just going to remove the UNION. Will I get the same results? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 13:21:54

it makes no sense for UNION ALL to be there as you're not merging resultsets but you're assigning them to OUTPUT parameters. So I think you could dispense with them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The Fire Snake
Starting Member

2 Posts

Posted - 2012-10-04 : 15:38:25

Thats exactly what I was thinking. The UNION ALL made no sense to me and I wanted to ask. Also even if you UNIONed the 2 things together, how would you return the result?

My only concern is if I remove the UNION, I hope I don't get different results than what I am getting now with the UNION.

Nothing like trying to debug crappy code that makes no sense. Seems like I see a lot of it these days...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 02:57:17

quote:
Originally posted by The Fire Snake

Thats exactly what I was thinking. The UNION ALL made no sense to me and I wanted to ask. Also even if you UNIONed the 2 things together, how would you return the result?

My only concern is if I remove the UNION, I hope I don't get different results than what I am getting now with the UNION.

Nothing like trying to debug crappy code that makes no sense. Seems like I see a lot of it these days...

You can get different results (without UNION operator)..........

CREATE PROCEDURE [dbo].[sp_MySP]
@p1 int OUTPUT,
@p2 int OUTPUT
AS
BEGIN

SELECT @p1 = count(*)
FROM <some table>
WHERE
(
<Some conditions>
)

SELECT @p2 = count(*)
FROM <same table>
WHERE
(
<Different conditions>
)

END

GO

-- Test Your Procedure

DECLARE @p int, @p2 int
EXEC [dbo].[sp_MySP] @p OUT, @p2 OUT
PRINT @p
PRINT @p2

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-06 : 00:38:53

quote:
Originally posted by bandi

quote:
Originally posted by The Fire Snake

Thats exactly what I was thinking. The UNION ALL made no sense to me and I wanted to ask. Also even if you UNIONed the 2 things together, how would you return the result?

My only concern is if I remove the UNION, I hope I don't get different results than what I am getting now with the UNION.

Nothing like trying to debug crappy code that makes no sense. Seems like I see a lot of it these days...

You can get different results (without UNION operator)..........

CREATE PROCEDURE [dbo].[sp_MySP]
@p1 int OUTPUT,
@p2 int OUTPUT
AS
BEGIN

SELECT @p1 = count(*)
FROM <some table>
WHERE
(
<Some conditions>
)

SELECT @p2 = count(*)
FROM <same table>
WHERE
(
<Different conditions>
)

END

GO

-- Test Your Procedure

DECLARE @p int, @p2 int
EXEC [dbo].[sp_MySP] @p OUT, @p2 OUT
PRINT @p
PRINT @p2

--
Chandu

Having variables on both sides of the union is not even syntactically correct!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources