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
jturkingtonAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Looks like @jobid is a numeric data type.  If so, comparing it to '' is the same as comparing it to zero.  Remove the comparion to '' and just use NULL:

AND
     (((@jobid IS NULL) AND


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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)
0
 
jturkingtonAuthor Commented:
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)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Aneesh RetnakaranDatabase AdministratorCommented:
did you tried mine
0
 
Scott PletcherSenior DBACommented:
You remove the wrong check.  Remove the '' check, leave the IS NULL comparison (please see my original post for sample code).
0
 
Scott PletcherSenior DBACommented:
aneesh:

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

Yep that did the trink nicely, any other tips to improve the WHERE condition ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi ScottPletcher,

try this

declare @i int
select len(@i)
set @i =12
select len(@i)
0
 
Scott PletcherSenior DBACommented:
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).
0
 
Scott PletcherSenior DBACommented:
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?

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 ..
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hey Scott,

  (@jobid IS NULL)  

 will  be equivalent to

(LEN(@jobid) =0 )

am I right ?
0
 
jturkingtonAuthor Commented:
Would that not be NULL = 0 ?
0
 
Scott PletcherSenior DBACommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.