Best way to search

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]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>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
>