TSQL: conditional union statement

[Thomas Baxter]

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

[Simon Hayes]

Thomas Baxter <qw*@ert.zxc> wrote in message news:<MP******* *************** **@freenews.iin et.net.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

[David Portas]

SELECT *
FROM territories
WHERE regionid = 1
OR (regionid = 2 AND @both = 1)

--
David Portas
------------
Please reply only to the newsgroup
--

[Gert-Jan Strik]

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

[Thomas Baxter]

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.iin et.net.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

[Thomas Baxter]

Hi David,

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

Tom

In article <GN************ ********@gigane ws.com>,
RE************* *************** @acm.org says...

SELECT *
FROM territories
WHERE regionid = 1
OR (regionid = 2 AND @both = 1)