Results 1 to 3 of 3

Thread: Adv Search

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

    Unanswered: Adv Search

    Hi all I am trying to create a search function for the users, they need to have the ability to search by any part of the field specifially for the Social
    like the last four digits, that sort of thing and i am having difficulties doing this. For the ADP a function would work better and for the Report services a Stored Procedure would works better.


    Code:
    CREATE FUNCTION [dbo].[SocialSecurityLookup](@ENTER_SSN# int)
    RETURNS TABLE
    AS
    RETURN ( SELECT     dbo.EmployeeG.TM#, dbo.EmployeeG.LASTNAME, dbo.EmployeeG.FIRSTNAME, 
                          dbo.EmployeeDoc.EmployeeDocs, dbo.EmployeeG.CAT, dbo.EmployeeG.JOBTITLE, 
                          dbo.EmployeeG.FINALSUITDONE
    FROM        dbo.EmployeeGLEFT OUTER JOIN
                          dbo.EmployeeDoc ON dbo.EmployeeG.TM# = dbo.EmployeeGTM#
    WHERE     (dbo.EmployeeG.SSN# = @ENTER_SSN#) )

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to modify the underlying table and add a computed column for you SSN:

    alter table dbo.EmployeeG add SSNLastFour as reverse(cast(reverse([SSN#]) as char(4)))

    Then you can perform the search on that field directly (of course you create an index on it, preferably with included columns from your SELECT list).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

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

    Talking

    Quote Originally Posted by rdjabarov
    You need to modify the underlying table and add a computed column for you SSN:

    alter table dbo.EmployeeG add SSNLastFour as reverse(cast(reverse([SSN#]) as char(4)))

    Then you can perform the search on that field directly (of course you create an index on it, preferably with included columns from your SELECT list).
    THANK YOU SO MUCH rdjabarov your the BEST

Posting Permissions

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