Prev: Return value from stored procedure that returns a recordset
Next: Query to find when a total is hit
From: DavidC on 27 May 2010 16:38
I have a stored procedure that I want to do a UNION ALL if a parameter value
is passed. I have tried this and I get 2 errors as follows:

Incorrect syntax near the keyword 'UNION'.

Incorrect syntax near 'END'.

The stored procedure is too large to paste but the concept is shown below.
Any help is appreciated.

SELECT x, y, z
FROM tableA
WHERE (Branch = @Branch)

IF @Background = 1
BEGIN
UNION ALL
SELECT x, y, z
FROM tableB
WHERE (Branch = @Branch)
END


--
David
From: Q on 27 May 2010 17:09
One way is to use temp table:

IF OBJECT_ID('TempDB..#ReturnData') IS NOT NULL
DROP TABLE #ReturnData

CREATE TABLE #ReturnData (x int, y int, z int)

INSERT INTO #ReturnData
SELECT x, y, z
FROM tableA
WHERE (Branch = @Branch)

IF @Background = 1
BEGIN
INSERT INTO #ReturnData
SELECT x, y, z
FROM tableB
WHERE (Branch = @Branch)
END


SELECT x, y, z
FROM #ReturnData

Q

"DavidC" wrote:

> I have a stored procedure that I want to do a UNION ALL if a parameter value
> is passed. I have tried this and I get 2 errors as follows:
>
> Incorrect syntax near the keyword 'UNION'.
>
> Incorrect syntax near 'END'.
>
> The stored procedure is too large to paste but the concept is shown below.
> Any help is appreciated.
>
> SELECT x, y, z
> FROM tableA
> WHERE (Branch = @Branch)
>
> IF @Background = 1
> BEGIN
> UNION ALL
> SELECT x, y, z
> FROM tableB
> WHERE (Branch = @Branch)
> END
>
>
> --
> David
From: John Bell on 27 May 2010 17:11
On Thu, 27 May 2010 13:38:07 -0700, DavidC <dlchase(a)lifetimeinc.com>
wrote:

>I have a stored procedure that I want to do a UNION ALL if a parameter value
>is passed. I have tried this and I get 2 errors as follows:
>
>Incorrect syntax near the keyword 'UNION'.
>
>Incorrect syntax near 'END'.
>
>The stored procedure is too large to paste but the concept is shown below.
>Any help is appreciated.
>
>SELECT x, y, z
>FROM tableA
>WHERE (Branch = @Branch)
>
>IF @Background = 1
> BEGIN
> UNION ALL
> SELECT x, y, z
> FROM tableB
> WHERE (Branch = @Branch)
> END


Try:

SELECT x, y, z
FROM tableA
WHERE (Branch = @Branch)
UNION ALL
SELECT x, y, z
FROM tableB
WHERE (Branch = @Branch)
AND @Background = 1

John
From: Tom Cooper on 27 May 2010 17:10
Select x, y, z
From tableA
Where Branch = @Branch
Union All
Select x, y, z
From tableA
Where Branch = @Branch And @Background = 1;

Tom

"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:C81D17F2-2946-4929-A229-E66368B3EE66(a)microsoft.com...
>I have a stored procedure that I want to do a UNION ALL if a parameter
>value
> is passed. I have tried this and I get 2 errors as follows:
>
> Incorrect syntax near the keyword 'UNION'.
>
> Incorrect syntax near 'END'.
>
> The stored procedure is too large to paste but the concept is shown below.
> Any help is appreciated.
>
> SELECT x, y, z
> FROM tableA
> WHERE (Branch = @Branch)
>
> IF @Background = 1
> BEGIN
> UNION ALL
> SELECT x, y, z
> FROM tableB
> WHERE (Branch = @Branch)
> END
>
>
> --
> David

From: Plamen Ratchev on 27 May 2010 16:51
You cannot change the query with conditional IF. Try this:

SELECT x, y, z
FROM tableA
WHERE Branch = @Branch
UNION ALL
SELECT x, y, z
FROM tableB
WHERE Branch = @Branch
AND @Background = 1;

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Return value from stored procedure that returns a recordset
Next: Query to find when a total is hit