SP - Efficient "Find" method

[UB]

I want another efficient way to re-write this query. Note : @empid, @batchid and @name are not always passed to the SP

CREATE PROCEDURE FindEmployee
@empid INTEGER = NULL,
@batchid INTEGER = NULL,
@name VARCHAR(30) = '%'
AS

SELECT \* FROM Employees
WHERE  @empid LIKE COALESCE(CONVERT(varchar(3), @empid), '%')
AND    @batchid LIKE COALESCE(CONVERT(varchar(3), @batchid), '%')
AND    name LIKE ('%' + @name + '%'

1. If @empid and @batchid were not provided, is there a way to remove thoese columns from the where clause conditionally without writing a seperate query 2. What are the otherways to improve the performance of this kind of a query Thanks UB You may stop this individual, but you can't stop us all... after all, we're all alike. +++Mentor+++

[Daniel Turini]

As a general rule, don't use functions or the LIKE operator on your table columns or your query won't use any indexes. The easiest way of speeding this query is by replacing this (I assume there's a typo and there is no '@' outside the COALESCE):

WHERE empid LIKE COALESCE(CONVERT(varchar(3), @empid), '%')
AND batchid LIKE COALESCE(CONVERT(varchar(3), @batchid), '%')

for something like this:

WHERE empid BETWEEN @firstEmpid AND @lastEmpid
AND batchid BETWEEN @firstBatchid AND @lastBatchid

And do a bit of math to calculate the first and last ids. If this filter returns a small result set, then the slowest part (show below) of the query won't need to run against a lot of records and the query will be way faster.

AND name LIKE ('%' + @name + '%'

Yes, even I am blogging now!