• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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
0
jturkington
Asked:
jturkington
  • 6
  • 5
  • 3
1 Solution
 
Scott PletcherSenior 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now