473,379 Members | 1,170 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

TSQL: conditional union statement

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
Jul 20 '05 #1
5 21891
Thomas Baxter <qw*@ert.zxc> wrote in message news:<MP************************@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
Jul 20 '05 #2
SELECT *
FROM territories
WHERE regionid = 1
OR (regionid = 2 AND @both = 1)

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #3
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

Jul 20 '05 #4
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 <60**************************@posting.google.com >,
sq*@hayes.ch says...
Thomas Baxter <qw*@ert.zxc> wrote in message news:<MP************************@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

Jul 20 '05 #5
Hi David,

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

Tom

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

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID ...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
10
by: Ilik | last post by:
Hi all I'm trying to create a summery table of rain data that holds a record for every 0.1mm of rain in the following format: Station Name, Time, Value A, 2002-12-03 14:44:41.000, 0.1 A,...
4
by: Elroyskimms | last post by:
Using SQL 2000... tblCustomer: CustomerID int CompanyName varchar(20) HasRetailStores bit HasWholesaleStores bit HasOtherStores bit tblInvoiceMessages:
4
by: AA Arens | last post by:
I amde a helpdesk database and on the calls form I put record navigation buttons / / / / / / Is het possible to navigate / through the records with a certain status. Each call has a...
5
by: paulo | last post by:
Can anyone please tell me how the C language interprets the following code: #include <stdio.h> int main(void) { int a = 1; int b = 10; int x = 3;
4
by: eeb4u | last post by:
I am connecting to MS SQL 2000 from Red Hat EL4 using freetds and currently running queries to get counts on tables etc. When running SELECT queries I notice that the data returns and I have to...
1
by: veaux | last post by:
Question deals with linking tables in queries. I'm not a code writer so use the GUI for all my queries. Table 1 - Master Table 2 - Sub1 Table 3 - Sub 2 All 3 tables have the same key field....
1
by: Sagaert Johan | last post by:
Hi How can i create an sql server login (sql 2005 express) ? I have my TSQL code ready but how can i run it from c#? How can i execute TSQL code from Csharp, or are there better ways using some...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.