conditional WHERE sections.

Hi I have a query where I am reading in a bunch of veriables and only want to
run parts of the where clause if the variables are valid, (will be input in a
stored procedure)
I tried
WHERE
if @.var1 IS NULL
BEGIN
{
tablename.fieldname >= @.var1
}
END
if @.var2 IS NULL
BEGIN
{
AND tablename.fieldname >= @.var2
}
END
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
thanks.
Paul G
Software engineer.
Paul
Create a dynamic SQL statement and build the where clause. Then execute the
SQL statement:
declare @.sql varchar(8000)
set @.sql = 'select * from table where '
If @.var1 is null
set @.sql = @.sql + 'condition 1'
else
set @.sql = @.sql + 'condition 2'
exec @.sql
"Paul" wrote:

> Hi I have a query where I am reading in a bunch of veriables and only want to
> run parts of the where clause if the variables are valid, (will be input in a
> stored procedure)
> I tried
> WHERE
> if @.var1 IS NULL
> BEGIN
> {
> tablename.fieldname >= @.var1
> }
> END
> if @.var2 IS NULL
> BEGIN
> {
> AND tablename.fieldname >= @.var2
> }
> END
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
> thanks.
>
> --
> Paul G
> Software engineer.
|||There are several approaches for handling such requirements. Some of the
popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
Anith
|||Hi thanks for the information. Figured there may be several ways to the
solution.
"Anith Sen" wrote:

> There are several approaches for handling such requirements. Some of the
> popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
> --
> Anith
>
>
|||Ok looks like the dynamic SQL statement should work for what I am trying to
do. Thanks.
"Bruce" wrote:
[vbcol=seagreen]
> Paul
> Create a dynamic SQL statement and build the where clause. Then execute the
> SQL statement:
> declare @.sql varchar(8000)
> set @.sql = 'select * from table where '
> If @.var1 is null
> set @.sql = @.sql + 'condition 1'
> else
> set @.sql = @.sql + 'condition 2'
> exec @.sql
>
> "Paul" wrote:

Conditional JOIN

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.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
--|||(chandy@.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...mplexconditions.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ahh, I get it now, hadn't fully comprehended what the WHERE EXISTS was
doing. That seems to work nicely.

Thanks!

Concatenating Fields With NULL Values

Hey everyone,

This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.

Here is my current SELECT statement:

SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.

The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.

Can someone tell me what I am missing? Is there a function that I can use for this?

Thanks,

Use ISNULL ( check_expression , replacement_value ) function

SELECT ISNULL(m.FName,'') + SPACE(1) + ISNULL(m.MName,'') + SPACE(1) + ISNULL(m.LName,'') + ', ' + ISNULL(m.Credentials,'') AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

|||

Hmm, I had never seen the SPACE function :) Only thing to suggest to your code is that you probably want to include the SPACE function calls in the ISNULL to get rid of extra useless spaces:

ISNULL(m.FName + SPACE(1),'')

|||

Thanks to both of you. I have even used ISNULL in the past. I feel pretty stupid now.

The reason I am using SPACE(1) is because I have seen SQL treat ' ' as an empty string. I think it has to do with sp_dbcmptlevel.

|||

Here's what I actually ended up doing:

SELECT REPLACE(RTRIM(m.FName + SPACE(1) + ISNULL(m.MName, '')) + SPACE(1) + m.LName + ', ' + ISNULL(m.Credentials, '*'), ', *', '') AS Name
FROM tblMembers m
WHERE m.DRGInclude = 1

The RTRIM gets rid of the extra space when MName is NULL, and to get rid of the ', ' when Credentials is null, I used the REPLACE function.

|||

Check out the property 'CONCAT_NULL_YIELDS_NULL'

you can turn it OFF by the following command

SET CONCAT_NULL_YIELDS_NULL OFF
GO

Thanks,
Loonysan