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

    Unanswered: Wild Cards in My Function

    Hi all, my function is not working correctly I am trying to implement wildcards and its giving me fits. OPERATER ERROR I'M SURE

    Code:
    ALTER FUNCTION [dbo].[SearchByTM]
    (@Enter_TM# int)
    
    AS
    IF patindex('*', @Enter_TM#) > 0
    
    SET @Enter_TM# = replace(@Enter_TM#, '*', '%')
    RETURN ( SELECT      dbo.EmployeeGamingLicense.TM#, dbo.EmployeeGamingLicense.LASTNAME, dbo.EmployeeGamingLicense.FIRSTNAME, 
                            dbo.EmployeeGamingLicense.CAT, dbo.EmployeeGamingLicense.JOBTITLE, dbo.EmployeeDocuments.EmployeeDocs, 
                            dbo.EmpPics_Table.EmployeePics
    FROM          dbo.EmployeeGamingLicense INNER JOIN
                            dbo.EmployeeDocuments ON dbo.EmployeeGamingLicense.TM# = dbo.EmployeeDocuments.TM# INNER JOIN
                            dbo.EmpPics_Table ON dbo.EmployeeGamingLicense.TM# = dbo.EmpPics_Table.TM#
    WHERE      (dbo.EmployeeGamingLicense.TM#  LIKE @Enter_TM#) )

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if your parameter is an INT, how well it ever have have a "*" in it?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    to show all the TM#, am I not understanding you?? The TM# is the employee number

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Nevermind Thrasy I see what your saying now

    thank you

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I wanted to be able to show all the TM#'s as well as a particular one that the user enters in

    LIKE
    Code:
    SELECT TM#, LASTNAME, FIRSTNAME
    FROM EmployeeGamingLicense
    WHERE TM# LIKE '7%';
    Last edited by desireemm; 06-05-08 at 17:57.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what's the datatype of the TM# column?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

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

    Red face

    Quote Originally Posted by Thrasymachus
    what's the datatype of the TM# column?

    its not a NVARCHAR
    Its a INT

    so of course its not working, the same kind of Funciton on another table but its a NVARCHAR not an INT

    OOPS

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM dbo.sysobjects
       WHERE  id LIKE '1%'
    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that I never said that was efficient or that it was a good idea, just that it might give you ideas.

    -PatP

Posting Permissions

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