Hi,
I am trying to change an SP from dynamic SQL to proper SQL but I can't
figure a way to conditionally add extra parts to the statement. How
can I do the equivalent of the following?
DECLARE @arg NVARCHAR(10)
SELECT a.i, a.x
FROM aTable a
IF LEN(@arg)
BEGIN
INNER JOIN bTable b ON a.[id] = b.[id]
END
Conditionally adding the INNER JOIN is very easy when building up a SQL
string but I can't see how to do it in pure SQL?
Thanks. 5 14623
What do you intend by a conditional join? The purpose of a join is
usually to bring back some extra columns from additional tables. Static
queries also have static metadata (in other words always the same set
of columns are returned every time) so a "conditional" join such as you
have posted is really just a selection:
SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @arg = ''
(I'm assuming ID is unique in B otherwise your original query might
return duplicate rows).
In general you can use OR to implement optional criteria but this often
leads to sub-optimal query plans. You should consider using IF
statements to choose from a set of possible queries or just break up
the different queries into separate SPs to be called independently.
Either approach is usually preferable to dynamic SQL.
--
David Portas
SQL Server MVP
--
I possibly gave a less than clear example. Insert an additional join
on aTable:
SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b on a.[id] = b.[id]
IF (LEN(@arg) > 0)
BEGIN
INNER JOIN cTable c ON a.[id] = c.[id]
END
The condition is that if the parameter @arg is of length 0 then I do
not want to join to cTable but if it is greater than 0 then I do. It's
essentially a switch to impose additional restrictions upon the
recordset being returned. I am not changing the structure of what is
being selected, just _conditionally_ adding an extra filter on the
data.
Thanks.
SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b
ON a.[id] = b.[id]
WHERE EXISTS
(SELECT *
FROM cTable c
WHERE c.[id] = a.[id])
OR @arg = ''
Again, assuming ID is unique in C this is equivalent to an INNER JOIN.
--
David Portas
SQL Server MVP
--
(ch****@totalise.co.uk) writes: I possibly gave a less than clear example. Insert an additional join on aTable:
SELECT a.i, a.x FROM aTable a INNER JOIN bTable b on a.[id] = b.[id] IF (LEN(@arg) > 0) BEGIN INNER JOIN cTable c ON a.[id] = c.[id] END
The condition is that if the parameter @arg is of length 0 then I do not want to join to cTable but if it is greater than 0 then I do. It's essentially a switch to impose additional restrictions upon the recordset being returned. I am not changing the structure of what is being selected, just _conditionally_ adding an extra filter on the data.
And that is exactly what David's query achieved. He hinted that his query
might not get a good query plan, but in fact
SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @arg = ''
should perform very well when @arg is ''. SQL Server will understand
that it does have to access bTable at all. It will appear in the plan,
but a so-called startup expression prevents it from being accessed when
there is no need to. (Interested readers can find more details on
this in http://www.sommarskog.se/dyn-search....plexconditions.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Ahh, I get it now, hadn't fully comprehended what the WHERE EXISTS was
doing. That seems to work nicely.
Thanks! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alex |
last post by:
Hi,
I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.
Here is my code thus far:
...
|
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,...
|
by: Bob Stearns |
last post by:
As part of what I want in a report, I want the associated row from t2,
if there is one, with the largest date in a range based on the base row
from t1. I attempted the following:
left outer join...
|
by: Raghavendra R A V, CSS India |
last post by:
hie..
Do any one knows how to write a C program without using the
conditional statements if, for, while, do, switch, goto and even
condotional statements ? It would be a great help for me if...
|
by: Piper707 |
last post by:
Hi,
I need to know if there is any way of achieving conditional processing
for XSD elements.
---------------------------------------------------------------
<criteria></criteria> = a complex...
| |
by: Jakob Outzen |
last post by:
I have made a basic left join statement say "Select t.*, p.usenet_id
from usenet_group_1 u left join user_table t on u.id = t.usenet_id;
but only want the recordset to return values from user_table...
|
by: A.M |
last post by:
Hi,
I am using Python 2.4. I read the PEP 308 at:
http://www.python.org/dev/peps/pep-0308/
|
by: msrviking |
last post by:
Hello everybody,
After several attempts of writing the query, I had to post my
requirement in the forum.
Here is what I have, what I need and what I did.
Table A
Col1 Col2
1 Nm1
|
by: simon.harris |
last post by:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
SELECT
CLIENTS.,
Logfile.,
Logfile.,
SASTESTS.Cost,
SASTESTS.*CLIENTS. AS...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |