Stored Procedure Efficiency - Null Input Parameters and Where clauses

I like to minimize the number of stored procedures I have by having a bunch of input parameters all defaulting to NULL and then those same parameters included in the WHERE with a test for Equality or NULL.

How inefficient is this in relation to query performance - verses writing stored procedure for 1 specific tasks? e.g. Select by UserId only, select by Username/Password, select by FirstName/LastName...

Example:

CREATE PROC dbo.up_Select_User

  @UserId int = NULL
, @Username varchar(255) = NULL
, @Password varchar(255) = NULL
, @FirstName varchar(50) = NULL
, @LastName varchar(50) = NULL
, @IsActive bit = NULL


SELECT UserId, Username, FirstName, LastName, EmailAddress
FROM dbo.[User]
WHERE (UserId = @UserId OR @UserId IS NULL)
AND (Username = @Username OR @Username IS NULL)
AND (Password = @Password OR @Password IS NULL)
AND (FirstName = @FirstName OR @FirstName IS NULL)
AND (LastName = @LastName OR @LastName IS NULL)
AND (IsActive = @IsActive OR @IsActive IS NULL)

Some additional info - may not be relevant... I am using entity framework 4 to map to stored procedures using a mix of normal entities and complex types depending on the query results开发者_运维百科.

This is inefficient because it uses OR and the optimiser will default to a scan .

If you have a few 1000 rows, it'll be OK though.

Sometimes, this pattern works better because the ISNULL can become trivial

SELECT UserId, Username, FirstName, LastName, EmailAddress
FROM dbo.[User]
WHERE
    UserId = ISNULL(@UserId, UserId) AND
    Username = ISNULL(@Username , Username) AND 
    Password = ISNULL(@Password , Password ) ...

we use this way to filter

AND (CASE @UserId WHEN NULL THEN @UserId ELSE UserId END) = @UserId

We don't have performance problems with large data on SqlServer with the solution.
Some of the application views (like report overviews pages) have more than 20 filter settings and it works good with the CASE WHEN construct.

Edit: These SET options have to be set

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 

@gbn: Careful, the Version with ISNULL is not exactly the same. E.g. if parameter @FirstName is NULL

... AND (FirstName = @FirstName OR @FirstName IS NULL)

does not take care of FirstName values; result includes records where FirstName is NULL

while

... AND FirstName = ISNULL(@FirstName, FirstName)

result does NOT include records where FirstName is NULL