help with conditional union all syntax

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 2005 Forums

Transact-SQL (2005)

help with conditional union all syntax

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-25 : 19:24:23

Hi,

I have a query in which I am trying to conditionally UNION

Having some trouble figuring out the syntax on how to do this, or if my approach is just wrong.

Basically if the value of the variable below = 1 then I want to include the query into the UNION, if it is 0, then I want to skip the query and avoid the union.

Any help is greatly appreciated !!

Thanks once again

,
mike123

DECLARE @showMe_AddedPhoto tinyint, @showMe_AddedFriend tinyint, @showMe_ReceivedComment tinyint

SELECT @showMe_AddedPhoto = showMe_AddedPhoto, @showMe_AddedFriend = showMe_AddedFriend, @showMe_ReceivedComment = showMe_ReceivedComment

FROM [dbo].[tblStatusUpdates_PrivacySettings] WHERE userID = @userID

SELECT TOP (@topCount) *
FROM (

--@showMe_AddedPhoto
SELECT TOP 10 userID FROM tblPhotos

UNION ALL
--@showMe_AddedFriend
SELECT TOP 10 userID FROM tblFriends

UNION ALL
--@showMe_ReceivedComment
SELECT TOP 10 userID FROM tblComments

) a

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 02:42:37

Try

SELECT TOP (@topCount) *
FROM (

--@showMe_AddedPhoto
SELECT TOP 10 userID FROM tblPhotos WHERE @showMe_AddedPhoto =1

UNION ALL
--@showMe_AddedFriend
SELECT TOP 10 userID FROM tblFriends WHERE @showMe_AddedFriend =1

UNION ALL
--@showMe_ReceivedComment
SELECT TOP 10 userID FROM tblComments WHERE @showMe_ReceivedComment =1

) a

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-26 : 02:59:30

Hi Madhivanan,

Wow works great. I am just wondering, is there any unncessary overhead on this ? For example, is it even looking at the table on this ?

Thanks!
Mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 08:22:06

When contion is true, it just returns all data from the corresponding tables

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources