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]

Re: TSQL: conditional union statement

Thomas Baxter <qwe@ert.zxc> wrote in message news:<MPG.1a197 9235adff7439897 3a@freenews.iin et.net.au>...[color=blue]
> 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[/color]

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]

Re: conditional union statement

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

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

[Gert-Jan Strik]

Re: TSQL: conditional union statement

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:[color=blue]
>
> 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[/color]

[Thomas Baxter]

Re: TSQL: conditional union statement

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 <60cd0137.03111 00028.2dc4b7f7@ posting.google. com>,
sql@hayes.ch says...[color=blue]
> Thomas Baxter <qwe@ert.zxc> wrote in message news:<MPG.1a197 9235adff7439897 3a@freenews.iin et.net.au>...[color=green]
> > 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[/color]
>
> 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
>[/color]

[Thomas Baxter]

Re: conditional union statement

Hi David,

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

Tom

In article <GNKdnYq-FZS3MTKi4p2dnA@ giganews.com>,
REMOVE_BEFORE_R EPLYING_dportas @acm.org says...[color=blue]
> SELECT *
> FROM territories
> WHERE regionid = 1
> OR (regionid = 2 AND @both = 1)
>
>[/color]