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