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