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
>