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 <qwe@ert.zxc> wrote in message news:<MPG.1a1979235adff74398973a@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

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

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

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

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.0311100028.2dc4b7f7@posting.google.com >,
sql@hayes.ch says...
> Thomas Baxter <qwe@ert.zxc> wrote in message news:<MPG.1a1979235adff74398973a@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
>

Hi David,

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

Tom

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