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

    Red face Unanswered: Look Up Function gone wrong

    Hi all I am trying to give my users the ability to look up the TM# field or the LASTNAME field or the FIRSTNAME field but I'm getting error messages, just trying to create a lookup form. I'm missing what I'm doiing wrong OPERATOR ERROR

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


    Code:
    Msg 102, Level 15, State 1, Procedure SearchByTM, Line 2
    Incorrect syntax near '@Enter_TM#'.
    Msg 137, Level 15, State 2, Procedure SearchByTM, Line 15
    Must declare the scalar variable "@Enter_TM#".

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try removing the # from your variable name..?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by georgev
    Try removing the # from your variable name..?
    still getting an error message

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    how about putting brackets around the input parameters?
    Code:
    ALTER FUNCTION [dbo].[SearchByTM]
    (
    @Enter_TM# int = null,
    @Enter_LASTNAME NVARCHAR(255) = NULL,
    @Enter_FIRSTNAME NVARCHAR(255) = NULL
    )
    RETURNS TABLE

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    this is so frustration
    Code:
     ALTER FUNCTION [dbo].[SearchByTM]
    (
    @Enter_TM# int = null,
    @Enter_LASTNAME NVARCHAR(255) = NULL,
    @Enter_FIRSTNAME NVARCHAR(255) = NULL
    )
    
    RETURNS TABLE
    AS
    RETURN  (SELECT        EmployeeGamingLicense.REHIRE, EmployeeGamingLicense.CONDITIONAL, EmployeeGamingLicense.DATEOFCONDITIONAL, EmployeeGamingLicense.SSNLastFour, 
                          EmployeeGamingLicense.INVESTIGATOR_COND, EmployeeGamingLicense.STATUS, EmployeeGamingLicense.CAT, EmployeeGamingLicense.TM#, 
                          EmployeeGamingLicense.LASTNAME, EmployeeGamingLicense.FIRSTNAME, EmployeeGamingLicense.SSN#,
                          EmployeeGamingLicense.HIREDATE, EmployeeGamingLicense.JOBTITLE, EmployeeGamingLicense.EMPLOYEENOTES, 
                          EmployeeGamingLicense.NIGCLICAPPRVD, EmployeeGamingLicense.FINALSUITDONE, EmployeeGamingLicense.NOTES, 
                          EmployeeGamingLicense.REASONFORCOND, EmployeeDocuments.EmployeeDocs
    
    FROM                  EmployeeGamingLicense INNER JOIN
                          EmployeeDocuments ON EmployeeGamingLicense.TM# = EmployeeDocuments.TM#
    WHERE      
    		        (@Enter_LASTNAME is null or LASTNAME = @Enter_LASTNAME)
    			AND (@Enter_FIRSTNAME is null or FIRSTNAME = @Enter_FIRSTNAME)
    			AND (@Enter_TM# is null or [TM#] = @Enter_TM#)
    Now its telling me Msg 102, Level 15, State 1, Procedure SearchByTM, Line 22
    Incorrect syntax near ')'.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You are missing a closing )

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and, what george said, - replace @Enter_TM# with @Enter_TM. And for that matter, what's up with Enter_ prefix? Yes, it is entered and already has a value. Why not just @TM, @LASTNAME, and @FIRSTNAME?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

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

    Talking

    Never mind guys I got it, thanks for your help

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by rdjabarov
    ...and, what george said, - replace @Enter_TM# with @Enter_TM. And for that matter, what's up with Enter_ prefix? Yes, it is entered and already has a value. Why not just @TM, @LASTNAME, and @FIRSTNAME?
    ok thank you

Posting Permissions

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