Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Incorporating WildCards In Function

    Hi all I am trying to give the users the ability to use wild cards in a search. For example if they dont know how to spell someones first or last name they can enter Sm for the beginning of the name or LL for the end of the name. Does that make sense?? Or an * to show all the records. I'm having some problems doing that, here is what I have so far

    Code:
    ALTER FUNCTION [dbo].[SearchByTM_LSTANDFRST](@Enter_TM# int = NULL,
    @Enter_LASTNAME NVARCHAR(255) = NULL,
    @Enter_FIRSTNAME NVARCHAR(255) = NULL)
    RETURNS TABLE
    AS
    RETURN ( SELECT      dbo.EmployeeGamingLicense.REHIRE, dbo.EmployeeGamingLicense.CONDITIONAL, dbo.EmployeeGamingLicense.DATEOFCONDITIONAL, 
                            dbo.EmployeeGamingLicense.SSNLastFour, dbo.EmployeeGamingLicense.INVESTIGATOR_COND, dbo.EmployeeGamingLicense.STATUS, 
                            dbo.EmployeeGamingLicense.CAT, dbo.EmployeeGamingLicense.TM#, dbo.EmployeeGamingLicense.LASTNAME, 
                            dbo.EmployeeGamingLicense.FIRSTNAME, dbo.EmployeeGamingLicense.SSN#, dbo.EmployeeGamingLicense.HIREDATE, 
                            dbo.EmployeeGamingLicense.JOBTITLE, dbo.EmployeeGamingLicense.EMPLOYEENOTES, dbo.EmployeeGamingLicense.NIGCLICAPPRVD, 
                            dbo.EmployeeGamingLicense.FINALSUITDONE, dbo.EmployeeGamingLicense.NOTES, dbo.EmployeeGamingLicense.REASONFORCOND, 
                            dbo.EmployeeDocuments.EmployeeDocs
    FROM          dbo.EmployeeGamingLicense INNER JOIN
                            dbo.EmployeeDocuments ON dbo.EmployeeGamingLicense.TM# = dbo.EmployeeDocuments.TM#
    WHERE      (@Enter_LASTNAME IS NULL) AND (@Enter_FIRSTNAME IS NULL) AND (@Enter_TM# IS NULL) OR
                            (@Enter_LASTNAME IS NULL) AND (@Enter_TM# IS NULL) AND (dbo.EmployeeGamingLicense.FIRSTNAME = @Enter_FIRSTNAME) OR
                            (@Enter_FIRSTNAME IS NULL) AND (@Enter_TM# IS NULL) AND (dbo.EmployeeGamingLicense.LASTNAME = @Enter_LASTNAME) OR
                            (@Enter_TM# IS NULL) AND (dbo.EmployeeGamingLicense.FIRSTNAME = @Enter_FIRSTNAME) AND 
                            (dbo.EmployeeGamingLicense.LASTNAME = @Enter_LASTNAME) OR
                            (@Enter_LASTNAME IS NULL) AND (@Enter_FIRSTNAME IS NULL) AND (dbo.EmployeeGamingLicense.TM# = @Enter_TM#) OR
                            (@Enter_LASTNAME IS NULL) AND (dbo.EmployeeGamingLicense.FIRSTNAME = @Enter_FIRSTNAME) AND 
                            (dbo.EmployeeGamingLicense.TM# = @Enter_TM#) OR
                            (@Enter_FIRSTNAME IS NULL) AND (dbo.EmployeeGamingLicense.LASTNAME = @Enter_LASTNAME) AND 
                            (dbo.EmployeeGamingLicense.TM# = @Enter_TM#) OR
                            (dbo.EmployeeGamingLicense.FIRSTNAME = @Enter_FIRSTNAME) AND (dbo.EmployeeGamingLicense.LASTNAME = @Enter_LASTNAME) AND 
                            (dbo.EmployeeGamingLicense.TM# = @Enter_TM#) )

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Merely enclosing each evaluation in its own set of parenthesis is not going to ensure that the comparisons will be evaluated in the correct order. Your mix of evaluations interspersed with ANDs and ORs virtually guarantees incorrect results.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I see what your saying, clearly I need to dump the and's and or's

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to properly group the ANDs and ORs.
    And you need to use parenthesis more sparingly and more correctly. The parenthesis in the code you posted do absolutely nothing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok will do, I think I got it now. I see where the problem is thanks guys

    I got what I need now

    Code:
     ---sample---
    ALTER PROCEDURE [dbo].[AdvSrchWildCardsFirstName]
    (@Enter_SubjFirstName nvarchar(100))
    
    AS
    SET @Enter_SubjFirstName = REPLACE(@Enter_SubjFirstName, '*', '%')
    SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                            dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
    FROM          dbo.Revised_MainTable INNER JOIN
                            dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
    
     
    
       IF patindex( '*', @Enter_SubjFirstName) > 0 
    
          BEGIN
    
             SET @Enter_SubjFirstName = replace( @Enter_SubjFirstName, '*', '%' )
    
     
    
            SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                            dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
    
            FROM          dbo.Revised_MainTable INNER JOIN
                            dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
           
    
           WHERE (dbo.RevisedSubjects_Table.SubjFirstName LIKE @Enter_SubjFirstName + '%')
    		
    
    
          END
    Last edited by desireemm; 08-11-08 at 18:15.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by blindman
    Merely enclosing each evaluation in its own set of parenthesis is not going to ensure that the comparisons will be evaluated in the correct order. Your mix of evaluations interspersed with ANDs and ORs virtually guarantees incorrect results.


    your absolutely correct I dont need all those parenthesis

    Code:
    WHERE     (@Enter_LastName is null or dbo.TERMINATION.LastName = @Enter_LastName)
    		AND (@Enter_FirstName is null or dbo.TERMINATION.FirstName = @Enter_FirstName)
    		AND (@Enter_TM_# IS NULL OR dbo.TERMINATION.[TM #] = @Enter_TM_#))

    This in the WHERE CLAUSE does just fine

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by desireemm
    This in the WHERE CLAUSE does just fine
    It would, except for the extra parenthesis on the end...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •