Best way to search -- sql server programming

Jeremy Chapman
6/1/2006 8:48:15 PM

I have a stored procedure declared (shown below) The intent of the stored
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?

CREATE PROCEDURE dbo.pSearch
@strFirstName varchar(50) = NULL,
@strLastName varchar(50) = NULL, @iDay int = null, @iMonth int = NULL,
@iYear int = null

SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@strFirstName IS NULL OR [FirstName] Like @strFirstName + '%') AND
(@strLastName IS NULL OR [LastName] Like @strLastName + '%') AND
(@iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @iMonth) AND
(@iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @iDay) AND
(@iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]

GO

Roji. P. Thomas
6/2/2006 12:00:00 AM

Have a look at

http://www.sommarskog.se/dyn-search.html

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
[quoted text, click to view]

sql server programming : Best way to search