Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Search Interface SPROC design Question !

I have four search boxes :  - CandidateID, FirstName,LastName,Address

I am getting an error if i type in both a candidateid and say for example john in the firstname box.

The error:  -

Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value 'john' to a column of data type int.  

I want the user to type in any types of combination into any of the four search boxes without it crashing !
Candidateid box only allows INTEGERS and the rest are text based LIKE searches

THE SPROC: -


USE Intranet

GO

ALTER PROCEDURE spSelect_Candidate_Search
      @candidateid      INT,
      @firstname      VARCHAR(50),
      @lastname      VARCHAR(50),
      @address      VARCHAR(100),
      @SortBy       VARCHAR(20),
      @SortAscending       CHAR(1)

As

SET NOCOUNT ON

IF @SortAscending = 'A'
      BEGIN
            SELECT      candidateid,
                  firstname,
                  middlename,
                  lastname,
                  address,
                  mobileno,
                  workno
            FROM Candidates
            WHERE      candidateid = @candidateid OR
                  firstname LIKE ('%'+@firstname +'%') AND
                  lastname LIKE ('%'+@lastname +'%') AND
                  address LIKE ('%'+@address +'%') AND
                  ( @candidateid + @firstname + @lastname + @address != '')

            ORDER BY
                  CASE
                        WHEN @SortBy = 'Address' Then address
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE firstname
                  END
                  ASC
      END
ELSE
      BEGIN
            SELECT      candidateid,
                  firstname,
                  middlename,
                  lastname,
                  address,
                  mobileno,
                  workno
            FROM Candidates
            WHERE      candidateid = @candidateid OR
                  firstname LIKE ('%'+@firstname +'%') AND
                  lastname LIKE ('%'+@lastname +'%') AND
                  address LIKE ('%'+@address +'%') AND
                  ( @candidateid + @firstname + @lastname + @address != '')

            ORDER BY
                  CASE
                        WHEN @SortBy = 'Address' Then address
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE firstname
                  END
                  DESC
      END

Any Help would be much appreciated !!
0
jturkington
Asked:
jturkington
  • 6
  • 3
  • 2
  • +2
5 Solutions
 
adatheladCommented:
>> ( @candidateid + @firstname + @lastname + @address != '') <<

This is causing the prob as you are concatenating an INTEGER value (@candidateid) to a VARCHAR value (@firstname)

Try:

( CAST(@candidateid AS VARCHAR) + @firstname + @lastname + @address != '')
0
 
jturkingtonAuthor Commented:
Thanks adathelad
Any ideas on how to improve the query performance wise or design wise or is it okay ?

Cheers

JT
0
 
jturkingtonAuthor Commented:
Having problems trying to return results, if i type any combinations in nothing is returned ??

I also tried switching the
candidateid = @candidateid OR

to

candidateid = @candidateid AND

No Joy ??

HELP

JT
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Renante EnteraCommented:
Hi jturkington!

Try to use "OR" on all your conditions instead of "AND" :

  WHERE     candidateid = @candidateid
  OR firstname LIKE ('%'+@firstname +'%')
  OR lastname LIKE ('%'+@lastname +'%')
  OR address LIKE ('%'+@address +'%')
  OR (CAST(@candidateid AS VARCHAR) + @firstname + @lastname + @address != '')

Actually, it depends on your requirements.  If you want to return records that will correspond in every input params then use "AND" but if you want to return records that will match any of the input params then "OR" is the correct operator.

Hope this makes sense.


Goodluck!
eNTRANCE2002 :-)
0
 
jturkingtonAuthor Commented:
Thanks Entrance

Its returning everthing by default when i run the form now with all ORS and i cant filter anything !! ??

Okay the CF SPROC

<cfstoredproc procedure="spSelect_Candidate_Search" datasource="#request.dsn#">
      <cfprocparam type="In" maxlength="6" cfsqltype="cf_sql_integer" value="#VAL(FORM.candidateid)#" null="no">
      <cfprocparam type="In" maxlength="50" cfsqltype="cf_sql_varchar" value="#FORM.firstname#" null="no">
      <cfprocparam type="In" maxlength="50" cfsqltype="cf_sql_varchar" value="#FORM.lastname#" null="no">
      <cfprocparam type="In" maxlength="100" cfsqltype="cf_sql_varchar" value="#FORM.address#" null="no">
      <cfprocparam type="In" maxlength="20" cfsqltype="cf_sql_varchar" value="#FORM.SortBy#" null="no">
      <cfprocparam type="In" maxlength="1" cfsqltype="cf_sql_char" value="#FORM.AscDesc#" null="no">
      <cfprocresult name="Get_Candidate_Details" resultset="1" maxrows="500">
</cfstoredproc>

THE SPROC TO DATE

USE Intranet

GO

ALTER PROCEDURE spSelect_Candidate_Search
      @candidateid      INT,
      @firstname      VARCHAR(50),
      @lastname      VARCHAR(50),
      @address      VARCHAR(100),
      @SortBy       VARCHAR(20),
      @SortAscending       CHAR(1)

As

SET NOCOUNT ON

IF @SortAscending = 'A'
      BEGIN
            SELECT      candidateid,
                  firstname,
                  middlename,
                  lastname,
                  address,
                  mobileno,
                  workno
            FROM Candidates
            WHERE      candidateid = @candidateid OR
                  firstname LIKE ('%'+@firstname +'%') OR
                  lastname LIKE ('%'+@lastname +'%') OR
                  address LIKE ('%'+@address +'%') OR
                  ( CAST(@candidateid AS VARCHAR) + @firstname + @lastname + @address != '')

            ORDER BY
                  CASE
                        WHEN @SortBy = 'Address' Then address
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE firstname
                  END
                  ASC
      END
ELSE
      BEGIN
            SELECT      candidateid,
                  firstname,
                  middlename,
                  lastname,
                  address,
                  mobileno,
                  workno
            FROM Candidates
            WHERE      candidateid = @candidateid OR
                  firstname LIKE ('%'+@firstname +'%') OR
                  lastname LIKE ('%'+@lastname +'%') OR
                  address LIKE ('%'+@address +'%') OR
                  ( CAST(@candidateid AS VARCHAR) + @firstname + @lastname + @address != '')
            ORDER BY
                  CASE
                        WHEN @SortBy = 'Address' Then address
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE firstname
                  END
                  DESC
      END

What am i doing wrong ??

Cheers

JT

0
 
adatheladCommented:
I think you need this:

 WHERE     candidateid = @candidateid OR
               (firstname LIKE ('%'+@firstname +'%') AND
               lastname LIKE ('%'+@lastname +'%') AND
               address LIKE ('%'+@address +'%') AND
               ( @candidateid + @firstname + @lastname + @address != ''))

I've added brackets around all the clauses from firstname LIKE  to the end.
So, logically it will perform a search where the candidateid matches OR where the firstname, lastname and address fields are like the corresponding parameters passed in
0
 
jturkingtonAuthor Commented:
do i need the CAST for the candidateid still ?
0
 
adatheladCommented:
>> do i need the CAST for the candidateid still ? <<
Yes, sorry I left that out of my post by mistake
0
 
Renante EnteraCommented:
Hi jturkington!

Basing from this data :
------------------------------------------------------------------------------------
candidateid     | firstname     | middlename     | lastname     | address
------------------------------------------------------------------------------------
1                      Fname1          Mname1             Lname1         Address1
2                      Fname2          Mname2             Lname2         Address2
3                      Fname3          Mname3             Lname3         Address3
4                      Fname4          Mname4             Lname4         Address4

If this is the combination : candidateid = 1, firstname = 'Fn', lastname = 'Ln', address = 'Add'

What would be the expected result ???  Let me know.


Regards!
eNTRANCE2002 :-)
0
 
jturkingtonAuthor Commented:
Hmmm thats a good one im not sure, what way would you handle it entrance2002 ??

I assume that the candidateid record 1 will definately be returned, but i know the LIKE operater for the other three fields firstname,lastname and address will match all records !!

I would imagine that end users would want an exact match if they put in a reference number and it would be unlikely they would use the canddiateid field with any other combination, but if they did i would imagine they would just want to see the one record returned right ??

Thanks for that example entrance2002 !
0
 
jturkingtonAuthor Commented:
Okay getting weird results now, i think its the way i designed the query.

I want to achieve the following (entrance2002 example) :  -

If a candidateid is entered it should return one record or none
If any of the other three boxes are selected it performs like searches with all eg FIRSTNAME AND LASTNAME AND ADDRESS
If candidateid and any of the other LIKE boxes are filled in ONE record should be returned ?? no sure how this would work or any recommendations would be much appreciated !!

The CODE so Far

<!--- Selects All Candidates That Are Like The Firstname or Lastname or Address Entered By User  --->
<cfstoredproc procedure="spSelect_Candidate_Search" datasource="#request.dsn#">
      <cfprocparam type="In" maxlength="6" cfsqltype="cf_sql_integer" value="#FORM.candidateid#" null="#YesNoFormat(Form.candidateid EQ '')#">
      <cfprocparam type="In" maxlength="50" cfsqltype="cf_sql_varchar" value="#FORM.firstname#" null="no">
      <cfprocparam type="In" maxlength="50" cfsqltype="cf_sql_varchar" value="#FORM.lastname#" null="no">
      <cfprocparam type="In" maxlength="100" cfsqltype="cf_sql_varchar" value="#FORM.address#" null="no">
      <cfprocparam type="In" maxlength="20" cfsqltype="cf_sql_varchar" value="#FORM.SortBy#" null="no">
      <cfprocparam type="In" maxlength="1" cfsqltype="cf_sql_char" value="#FORM.AscDesc#" null="no">
      <cfprocresult name="Get_Candidate_Details" resultset="1" maxrows="500">
</cfstoredproc>

USE Intranet

GO

ALTER PROCEDURE spSelect_Candidate_Search
      @candidateid      INT,
      @firstname      VARCHAR(50),
      @lastname      VARCHAR(50),
      @address      VARCHAR(100),
      @SortBy       VARCHAR(20),
      @SortAscending       CHAR(1)

As

SET NOCOUNT ON

IF @SortAscending = 'A'
      BEGIN
            SELECT      candidateid,
                  firstname,
                  middlename,
                  lastname,
                  address,
                  towncity,
                  country,
                  mobileno,
                  status
            FROM Candidates
            WHERE      candidateid = @candidateid OR
                  (firstname LIKE ('%'+@firstname +'%') AND
                  lastname LIKE ('%'+@lastname +'%') AND
                  address LIKE ('%'+@address +'%') AND
                  ( CAST(@candidateid AS VARCHAR) + @firstname + @lastname + @address != ''))

            ORDER BY
                  CASE
                        WHEN @SortBy = 'Address' Then address
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE firstname
                  END
                  ASC
      END
ELSE
      BEGIN
            SELECT      candidateid,
                  firstname,
                  middlename,
                  lastname,
                  address,
                  towncity,
                  country,
                  mobileno,
                  status
            FROM Candidates
            WHERE      candidateid = @candidateid OR
                  (firstname LIKE ('%'+@firstname +'%') AND
                  lastname LIKE ('%'+@lastname +'%') AND
                  address LIKE ('%'+@address +'%') AND
                  ( CAST(@candidateid AS VARCHAR) + @firstname + @lastname + @address != ''))

            ORDER BY
                  CASE
                        WHEN @SortBy = 'Address' Then address
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE firstname
                  END
                  DESC
      END
0
 
rafranciscoCommented:
Try changing your WHERE clause above to this:

WHERE     candidateid = @candidateid OR
              ((@candidateID = '' OR @candidateID IS NULL) AND
                firstname LIKE ('%'+@firstname +'%') AND
                lastname LIKE ('%'+@lastname +'%') AND
                address LIKE ('%'+@address +'%'))
0
 
Anthony PerkinsCommented:
>>Any ideas on how to improve the query performance wise or design wise or is it okay ?<<
LIKE is more than likely forcing you into sequential search. Consider using Full Text Search instead.  
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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