Link to home
Start Free TrialLog in
Avatar of jturkington
jturkington

asked on

SQL Statement Design Question

Okay i have four intranet text boxes which i use to filter database values for a specified candidateid

Company | Firstname | Lastname | JobID

I want the page to bring back all records by default for the candidateid and filter accordingly after this. Company/ firstname/lastname will bring back records with the LIKE search but i want to override these fields if a user types in a jobid. I think this works okay at present but the problem i am having is when the user types in 0 for the jobid it just brings back ALLrecords for the candidate instead of jobids with the value of 0. The way i have designed my tables if a jobid wasn't inputted i set the value to 0 instead of leaving it as NULL.

Can someone give me any ideas how to tweak the below search condition to fix this ?

 
WHERE ca.candidateidfk = @candidateid
      AND
      (((@jobid = '' OR @jobid IS NULL) AND
        c.companyname LIKE ('%'+@company +'%') AND
        ct.firstname LIKE ('%'+@firstname +'%') AND
        ct.lastname LIKE ('%'+@lastname +'%'))
      OR
        ca.jobidfk = @jobid)

Cheers

JT
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this

WHERE ca.candidateidfk = @candidateid
     AND
     (((LEN(@jobid) =0 ) AND
       c.companyname LIKE ('%'+@company +'%') AND
       ct.firstname LIKE ('%'+@firstname +'%') AND
       ct.lastname LIKE ('%'+@lastname +'%'))
     OR
       ca.jobidfk = @jobid)
Avatar of jturkington
jturkington

ASKER

Doesn't return anything when the form first loads now ??

WHERE      ca.candidateidfk = @candidateid
      AND
      (((@jobid = '') AND
        c.companyname LIKE ('%'+@company +'%') AND
        ct.firstname LIKE ('%'+@firstname +'%') AND
        ct.lastname LIKE ('%'+@lastname +'%'))
      OR
        ca.jobidfk = @jobid)
did you tried mine
You remove the wrong check.  Remove the '' check, leave the IS NULL comparison (please see my original post for sample code).
aneesh:

LEN() is not designed for use on a numeric column, only strings, so will only return NULL (I assume).
Sorry ScottPletcher too much Caffeine today !! LOL

Yep that did the trink nicely, any other tips to improve the WHERE condition ?
Hi ScottPletcher,

try this

declare @i int
select len(@i)
set @i =12
select len(@i)
If there's any way to remove the leading % in the LIKEs that will help tremendously; of course, as I'm sure you know, you can *only* do that if the string you're searching for is at the *start* of the column, which it often isn't :-( .  As long as you have to use the % in the front, a full table scan will be required.  

Another good option would be a full-text search, which would be much faster and more powerful.  And full-text searching is free to use with SQL std and above (at least; don't know about developer, other "lower" versions).
aneesh:
>> try this .... <<

Interesting; looks like SQL implicitly converts the numeric value to a varchar.  Wasn't aware of that, since I haven't used that function on the wrong data type.

But that should mean a value of 0 will *also* have a len of 1.  So the LEN() of an INT can *never* be 0, can it?  It will either be NULL or 1+, won't it?

ScottPletcher,
> So the LEN() of an INT can *never* be 0, can it?  It will either be  NULL or 1+, won't it?
Yes, You are right..I am bit confused  with the question ..
Hey Scott,

  (@jobid IS NULL)  

 will  be equivalent to

(LEN(@jobid) =0 )

am I right ?
Would that not be NULL = 0 ?
No, they are not equivalent.  As jturkington points out, that would equate to "NULL  0", which is *not* true.

At any rate, using LEN() on an integer column is just bad practice.  No one will inherently know what it does, since it's not an expected thing to do.