Link to home
Start Free TrialLog in
Avatar of jturkington
jturkington

asked on

Designing a SPROC for Searching & Ordering By Fields

Hello ! I am trying to improve the following SPROC, i am trying to solve the issue if no search terms are entered  eg... firstname is "" and lastname is "" then no records are returned. But with the SPROC below it is returning every single record in the contacts table which slows things down on the frontend (Coldfusion MX).

Also if anybody has tips on how to improve the ordering by, sort ascending/descending or search sections it would be much appreciated !!

The Code: -

USE Intranet

GO

ALTER PROCEDURE spSelect_Contact_Search
      @Firstname      VARCHAR(50),
      @Lastname      VARCHAR(50),
      @SortBy                       VARCHAR(20),
      @SortAscending       CHAR(1)

As

SET NOCOUNT ON

IF @SortAscending = 'A'
      BEGIN
            SELECT      
                  ct.contactid,
                  ct.firstname,
                  ct.lastname,
            FROM Contacts ct
            WHERE      firstname LIKE ('%'+@firstname +'%') AND
                  lastname LIKE ('%'+@lastname +'%')
            ORDER BY
                  CASE
                        WHEN @SortBy = 'FirstName' Then firstname
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE mobileno
                  END
                  ASC
      END
ELSE
      BEGIN
            SELECT      
                  ct.contactid,
                  ct.firstname,
                  ct.lastname,

            FROM Contacts ct

            WHERE      firstname LIKE ('%'+@firstname +'%') AND
                  lastname LIKE ('%'+@lastname +'%')
            ORDER BY
                  CASE
                        WHEN @SortBy = 'FirstName' Then firstname
                        WHEN @SortBy = 'LastName' Then lastname
                        ELSE mobileno
                  END
                  DESC
      END
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jturkington
jturkington

ASKER

Cheers rafrancisco !