469,110 Members | 2,029 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

TSQL: conditional union statement

Is it possible to have a conditional union statement in a stored proc?

Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.

Is the only solution to create a dynamic sql string then call exec on
it?

Any help appreciated.

Tom.

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

SELECT * FROM territories WHERE regionid = 1

IF @both = 1
BEGIN

UNION

SELECT * FROM territories WHERE regionid = 2

END
GO
Jul 20 '05 #1
5 21495
Thomas Baxter <qw*@ert.zxc> wrote in message news:<MP************************@freenews.iinet.ne t.au>...
Is it possible to have a conditional union statement in a stored proc?

Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.

Is the only solution to create a dynamic sql string then call exec on
it?

Any help appreciated.

Tom.

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

SELECT * FROM territories WHERE regionid = 1

IF @both = 1
BEGIN

UNION

SELECT * FROM territories WHERE regionid = 2

END
GO


This is one possible solution:

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

if @both = 1
SELECT * FROM territories WHERE regionid = 1
UNION
SELECT * FROM territories WHERE regionid = 2

else
SELECT * FROM territories WHERE regionid = 1

GO

Simon
Jul 20 '05 #2
SELECT *
FROM territories
WHERE regionid = 1
OR (regionid = 2 AND @both = 1)

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #3
As you saw from the answers, it is not possible to do a condition UNION

However, here is another work around:

SELECT * FROM territories WHERE regionid = 1
UNION
SELECT * FROM territories WHERE regionid = 2 AND @both = 1

HTH,
Gert-Jan
Thomas Baxter wrote:

Is it possible to have a conditional union statement in a stored proc?

Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.

Is the only solution to create a dynamic sql string then call exec on
it?

Any help appreciated.

Tom.

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

SELECT * FROM territories WHERE regionid = 1

IF @both = 1
BEGIN

UNION

SELECT * FROM territories WHERE regionid = 2

END
GO

Jul 20 '05 #4
Thanks Simon,

While that'a good idea, my actual script is HUGE, so maintenance would
become a problem having two copies of the same stuff.

Thanks for the reply though.

Tom

In article <60**************************@posting.google.com >,
sq*@hayes.ch says...
Thomas Baxter <qw*@ert.zxc> wrote in message news:<MP************************@freenews.iinet.ne t.au>...
Is it possible to have a conditional union statement in a stored proc?

Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.

Is the only solution to create a dynamic sql string then call exec on
it?

Any help appreciated.

Tom.

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

SELECT * FROM territories WHERE regionid = 1

IF @both = 1
BEGIN

UNION

SELECT * FROM territories WHERE regionid = 2

END
GO


This is one possible solution:

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

if @both = 1
SELECT * FROM territories WHERE regionid = 1
UNION
SELECT * FROM territories WHERE regionid = 2

else
SELECT * FROM territories WHERE regionid = 1

GO

Simon

Jul 20 '05 #5
Hi David,

Thanks, that will do what I'm after with a bit of fiddling.

Tom

In article <GN********************@giganews.com>,
RE****************************@acm.org says...
SELECT *
FROM territories
WHERE regionid = 1
OR (regionid = 2 AND @both = 1)

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Paradigm | last post: by
10 posts views Thread by Ilik | last post: by
4 posts views Thread by Elroyskimms | last post: by
4 posts views Thread by AA Arens | last post: by
5 posts views Thread by paulo | last post: by
4 posts views Thread by eeb4u | last post: by
1 post views Thread by Sagaert Johan | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.