USING SQL IF Statement in WHERE clause

rit01 · February 14th, 2006, 05:37 AM

HI Imar,

I'll give your theory a go and let you know how I get on. My full query does in include 2 conditional WHERE clauses (as in above) and some standard comparison ones so brackets will come in handy.

Rit

rit01 · February 14th, 2006, 06:14 AM

I can see a www.tributes2imar.com in the making!.

Imar, thanks very much, it worked great and easily digestable for my mind.

Rit

rit01 · May 30th, 2006, 07:02 AM

Hi All

Based on Imar's theory of:

SELECT SomeField FROM SomeTable
WHERE SomeField = @someParam OR @someParam = 1

How could I set this up to work with NULLs i.e.

@someParam INT

SELECT SomeField FROM SomeTable
WHERE SomeField = @someParam OR @someParam = NULL

When I try it it doesn't respond how I would have expected it to.. It seems to have a problem with NULL and doesn't return any records.

Many thanks

Rit

Jeff Mason · May 30th, 2006, 07:19 AM

No value, parameter or table column, can ever be equal to NULL, regardless of what value is stored there (even NULL).

You need to test if a value IS NULL, as:

SELECT SomeField FROM SomeTable
WHERE SomeField = @someParam OR @someParam IS NULL

This could also be written equivalently as:

SELECT SomeField FROM SomeTable
WHERE SomeField = coalesce(@someParam, SomeField)

This works becuase if @someParam IS NULL, then the coalesce function will return the value of SomeField, thus testing a column value for equality with itself, which of course is always true.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ClaudiaN · February 1st, 2007, 01:13 PM

How can I do this same thing outside of a stored procedure (In a simple select statement)?
What I need to do is select * where name = Param or Param = ALL (to return one name or everyone).
Thanks,
Claudia

Quote:

quote:Originally posted by Imar
Hi Rit,

You don't need an IF for this. You can simply compare the *parameter* with the required value.

E.g.:

SELECT SomeField FROM SomeTable
WHERE SomeField = @someParam OR @someParam = 1

So, when you pass 1 for the @someParam param, all records are returned, because 1 always equals one....

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

ClaudiaN · February 1st, 2007, 01:24 PM

Never mind. I've answered my own question.
Thanks anyways.

Imar · February 1st, 2007, 05:33 PM

Would you mind posting *how* you answered your own question?

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

jdziggy · March 15th, 2007, 08:33 AM

Hey all,
I am trying to convert the following code into a stored procedure. I wonder if any of you could help me. It appears that it is saying if a string startswith "z_" then replace "z_" with "" and if Count is = 0 then use the first Where clause with the proceeding SQL statement else the second where clause.

For each item in Request.QueryString
                    if Left(item,2)="z_" Then
                        intCurrentVar = Replace(item, "z_", "")
                        if iCount = 0 Then
                            strWhereClause = " WHERE tblPCRDdata.Option_ID=" & intCurrentVar

                        else
                            strWhereClause = strWhereClause & " or tblPCRDdata.Option_ID=" & intCurrentVar
                        end if

                        End if

                    iCount = iCount + 1
                Next

               strReportSQL = "SELECT tblMain.*, tblAgency.Name AS Agency " _
                                & " FROM tblMain INNER JOIN " _
                                & " tblPCRDdata ON tblMain.ID = tblPCRDdata.Report_ID INNER JOIN " _
                              & " tblAgency ON tblMain.HHSAgency = tblAgency.ID " & strWhereClause

Code?  We don't need no stinkin' code!