[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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
0
jturkington
Asked:
jturkington
1 Solution
 
rafranciscoCommented:
Try this to solve the issue on returning nothing if both first name and last name are blanks:

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 +'%') AND ( @firstname + @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 +'%') AND ( @firstname + @lastname != '')
          ORDER BY
               CASE
                    WHEN @SortBy = 'FirstName' Then firstname
                    WHEN @SortBy = 'LastName' Then lastname
                    ELSE mobileno
               END
               DESC
     END
0
 
jturkingtonAuthor Commented:
Cheers rafrancisco !
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now