Neater SQL Query for handling Optional WHERE Clause Filters

Please see the SQL statement below:

Is there any better way to do this my eliminating the case statements?

select * from Customer 
where FirstName like ISNULL('ja','') + '%'  
AND [EmailId] LIKE ISNULL('jaisonshereen@gmail.com1','') 
    + CASE when 'jaisonsh开发者_开发知识库ereen@gmail.com1' =  '' then '%' else ''  end

Based on @Thor84no's observation, I've parameterised your query (assuming that it is code generated) and assumed the following requirements:

• Both @Firstname and @Email are optional
• If @Firstname is not null or blank, always suffix it with % and do a like, otherwise, apply a dummy filter (LIKE '%')
• If @Email is not null or blank then do an exact search (Like @EMAIL), otherwise apply a dummy filter (LIKE '%')

So it parameterises like such:

DECLARE @FirstName NVARCHAR(100)
DECLARE @EmailId NVARCHAR(100)
SET @FirstName = 'ja'
SET @EmailId = 'jaisonshereen@gmail.com1'

select * from Customer 
where FirstName like ISNULL(@FirstName,'') + N'%'  
AND [EmailId] LIKE ISNULL(@EmailId, N'') 
    + CASE when @EmailId =  N'' then N'%' else N''  end

I believe there is a case which you don't handle however, viz if @Email is NULL - you'll need to change the last line to

+ CASE when IsNull(@EmailId, '') =  N'' then N'%' else N''  end

The query plan of your code quite good - it will always be WHERE FirstName LIKE '..%' AND EMailId Like '..' (or EmailId Like '%') - this is probably why the code generator does this.

Although it is tempting to do the below for readability, the 'OR' hurts the query plan and generally results in table / index scans

select * from Customer 
WHERE 
(ISNULL(@FirstName, N'') = N'' OR FirstName LIKE @FirstName + N'%')
AND (ISNULL(@EmailId, N'') = N'' OR [EmailId] = @EmailId) -- Assuming ANSI Nulls are ON

So although what you've got looks messy, it is actually quite optimal.

Out of interest, dynamic SQL, such as generated by ORMS like LINQ2SQL, EF etc often has an advantage over a Stored Proc in cases where a large number of parameters are optional. By using parameterised SQL, query plans can still be cached, and the query is protected against SQL Injection attacks. Compare

DECLARE @FirstName NVARCHAR(100)
DECLARE @EmailId NVARCHAR(100)
SET @FirstName = 'ja'
SET @EmailId = 'jaisonshereen@gmail.com1'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM Customer '

IF ISNULL(@FirstName,'') <> N'' OR ISNULL(@EmailId, N'') <> N''
    SET @SQL = @SQL + N'WHERE ' -- Need to handle the case where neither param provided

IF ISNULL(@FirstName, N'') <> N''
    SET @SQL = @SQL + N' FirstName LIKE @FirstName + ''%'''

IF ISNULL(@FirstName,'') <> N'' AND ISNULL(@EmailId, N'') <> N''
    SET @SQL = @SQL + N' AND'

IF ISNULL(@EmailId,'') <> N''
    SET @SQL = @SQL + N' EmailId = @EmailId' -- Exact match

exec sp_ExecuteSQL @SQL, N'@FirstName NVARCHAR(100), @EmailId NVARCHAR(100)', @FirstName=@FirstName, @EmailId=@EmailId