Stored Procedure Performance Optimization

Hello,

I have a question regarding stored procedure desing that provides the
optimal performance. Let's say we have a table Products that consists of
three columns: Name, Status, RegistrationTime. All columns are indexed and
users should be able to lookup data by any of the columns. We have two main
options to design stored procedures for data retrieval:

1. Design separate stored procedures for each search criteria:
LookupProductsByName, LookupProductsByStatus, LookupProductsByTime.

2. Write a generic stored procedure that will fit any search criteria:

CREATE PROCEDURE GetProducts (
@Name varchar(20),
@Status int = NULL,
@FromTime datetime = NULL,
@ToTime datetime = NULL)
AS BEGIN
SELECT
[Name],
[Status],
[RegistrationTime]
FROM [Products]
WHERE [Name]=CASE
WHEN @Name<>NULL THEN @Name
ELSE [Name]
END
AND [Status]=CASE
WHEN @Status<>NULL THEN @Status
ELSE [Status]
END
AND [RegistrationTime]>=CASE
WHEN @FromTimestamp<>NULL THEN @FromTimestamp
ELSE [RegistrationTime]
END
AND [RegistrationTime]<=CASE
WHEN @ToTimestamp<>NULL THEN @ToTimestamp
ELSE [RegistrationTime]
END
ORDER BY [RegistrationTime]
END;

The second option is very attractive, because it is obviously easier to
maintain such code. However, I am a little concerned about performance of
such stored procedure. It is not possible to foresee what index should be
used, index can only be selected each during procedure execution, because
search criteria can include either Name, Status or RegistrationTime. Will it
make this SP inefficient? Or perormance difference in such case is not big
(if any) and we should choose the second option because of its significant
code reduction?

Thanks in advance

Vagif Abilov
Join Bytes!