Add WHERE clauses to SQL dynamically / programmatically

How can I add search condition to SQL Stored Procedure programmatically? In my application(C#) I’m using stored procedure (SQL Server 2008R2)

ALTER PROCEDURE [dbo].[PROC001]
@userID varchar(20),
@password varchar(20)
AS
SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password

I want to extend this query by more conditions, and now I don’t know how many conditions will use this query due program execution.. 2, 3, 6 OR 20. I want to add these conditions programmatically like:

SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password
AND Field2 = '1' AND Field3 = '0' OR Field4 <> '8' AND Field5 < '100' ....

Is it possible to sent conditions to stored procedure dynamically?

Answer

Edit – Preference for LINQ based ORM’s, if possible

If you don’t need to do this in ADO, a better solution is to use an ORM which will ultimately build parameterized ad-hoc sql. This is the best of both worlds – you get the flexibility of a dynamic query, with no redundant filters to upset the optimizer, the query plan itself is cacheable, and you are safe from nasties like injection attacks. And a Linq-based ORM query makes for easy reading:

 // Build up a non-materialized IQueryable<>
 var usersQuery = db.Users;
 if (!string.IsNullOrEmpty(userID))
 {
       usersQuery = usersQuery.Where(u => u.Name == userId);
 }
 // Of course, you wouldn't dream of storing passwords in cleartext.
 if (!string.IsNullOrEmpty(anotherField))
 {
       usersQuery = usersQuery.Where(u => u.AnotherColumn == anotherField);
 }
 ...
 // Materialize (and execute) the query
 var filteredUsers = usersQuery.ToList();

For complex queries, you may want to look at PredicateBuilder

ADO / manual query building

You can use sp_executesql to build up SQL dynamically as per below. Provided that you parameterize the variables you should be safe from issues like SQL injection and escaping quotes etc will be handled for you.

CREATE PROCEDURE [dbo].[PROC001]
    @userID varchar(20),
    @pwdHash varchar(20),
    @optionalParam1 NVARCHAR(50) = NULL -- Other optional parameters
AS        
    BEGIN        
        SET NOCOUNT ON        

        DECLARE @SQL NVARCHAR(MAX)        

        -- Mandatory / Static part of the Query here. 
        -- Cleartext passwords are verboten, and RTRIM is redundant in filters
        SET @SQL = N'SELECT * FROM tUsers WHERE Name = @userID AND PwdHash = @pwdHash'

        IF @OptionalParam1 IS NOT NULL        
            BEGIN        
                SET @SQL = @SQL + N' AND AnotherField = @OptionalParam1'    
            END        

        EXEC sp_executesql @SQL,        
            N'@userID varchar(20),
            @pwdHash varchar(20),
            @optionalParam1 NVARCHAR(50)'
            ,@userID = @userID
            ,@pwdHash = @pwdHash
            ,@optionalParam1 = @optionalParam1
    END

Re, why is WHERE (@x IS NULL OR @x = Column) a bad idea?

(From my comment below)

Although the ‘optional parameter’ pattern works well as a ‘swiss army knife’ for querying a multitude of permutations of optional filters when used on small tables, unfortunately, for large tables, this results in a single query plan for all permutations of filters for the query, which can result in poor query performance with certain permutations of optional parameters due to the parameter sniffing problem. If possible, you should eliminate redundant filters entirely.

Re: Why is applying functions in predicates a bad idea

e.g.

WHERE SomeFunction(Column) = @someParameter

Use of functions in predicates frequently disqualifies the use of indexes by the RDBMS (“non-sargable”).

In this instance, RTRIM is unnecessary as Sql server ignores trailing spaces during comparison.