Optimized SQL for Name Search
-
Looking for optimized SQL for a Name Search Screen. Specs are straight forward:
Provide capability for User to Search CUSTOMER table on any combination of 3 columns:
- FirstName nvarchar (30) NULL
- LastName nvarchar (30) NULL
- eMail nvarchar (50) NULL
> At least 1 column WILL be available to search
> Unavailble columns will be NULL
> Wild Cards are available (eg. Search where LastName like 'Thom%')
thx in advance-
BT -
Try to use this inline query building technique (the fastest way to do it):
DECLARE @query NVARCHAR(300)
DECLARE @fname NVARCHAR(30)
DECLARE @lname NVARCHAR(30)
DECLARE @email NVARCHAR(50)
SET @fname = 'koko'
SET @email = 'koko@gogo.com'
SET @query = ''
SET @query = @query + 'SELECT * '
SET @query = @query + 'FROM CUSTOMERS '
SET @query = @query + 'WHERE 1 = 1 '
IF @fname IS NOT NULL
SET @query = @query + ' AND FirstName LIKE ''%' + @fname + '%'''
IF @lname IS NOT NULL
SET @query = @query + ' AND LastName LIKE ''%' + @lname + '%'''
IF @email IS NOT NULL
SET @query = @query + ' AND email LIKE ''%' + @email + '%'''
PRINT @query
Ezz Khayyat
-
Please make sure you understand the implications of dynamic sql before going down that path.
Make sure you read this about The Curse and Blessings of Dynamic SQL
Which will lead you to Dynamic Search Conditions in T-SQL
-
CREATE PROC SearchCustomer
(
@pFname VARCHAR(30) = NULL,
@pLname VARCHAR(30) = NULL,
@pEmail VARCHAR(50) = NULL,
)
AS
SET NOCOUNT ON
SELECT * FROM CUSTOMERS
WHERE
Fname LIKE '%' + RTRIM(LTRIM(COALESCE(@pFname,''))) + '%' AND
Lname LIKE '%' + RTRIM(LTRIM(COALESCE(@pLname,''))) + '%' AND
Email LIKE '%' + RTRIM(LTRIM(COALESCE(@pEmail,''))) +'%'
GO
Regards,
gova -
I usually like to go one step further so that if you have a lot of search parameters that are optional you can speed up the search, but not searching on the parameters that are not included.
SELECT * FROM CUSTOMERS
WHERE
(Fname IS NULL OR (Fname IS NOT NULL AND Fname LIKE '%' + RTRIM(LTRIM(@pFname)) + '%')) AND
(Lname IS NULL OR (Lname IS NOT NULL AND Lname LIKE '%' + RTRIM(LTRIM(@pLname)) + '%')) AND
(Email IS NULL OR (Email IS NOT NULL AND Email LIKE '%' + RTRIM(LTRIM(@pEmail)) +'%'))
-
That will list all the values that are null and match the search criteria.
For not including the parameters that are not passed got to use a CASE or IF ELSE with different queries.
LIKE '%%' will exclude nulls so there is no need for a Fname IS NOT NULL
Regards,
gova -
You are correct, I made the assumption that the data that you are searching on would be non-NULLable and should have stated that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply