SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about stored procedure and Union All
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

The Fire Snake
Starting Member

2 Posts

Posted - 10/04/2012 :  13:04:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/04/2012 :  13:21:54  Show Profile  Reply with Quote
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/

Go to Top of Page

The Fire Snake
Starting Member

2 Posts

Posted - 10/04/2012 :  15:38:25  Show Profile  Reply with Quote
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...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 10/05/2012 :  02:57:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/06/2012 :  00:38:53  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000