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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER