c# - Add WHERE clauses to SQL dynamically / programmatically

Question

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 1

You can do this in sql only, like this:

SELECT * 
FROM tUsers 
WHERE 1 = 1
  AND (@userID IS NULL OR RTRIM(Name) = @userID )
  AND (@password IS NULL OR RTRIM(Password) = @password)
  AND (@field2 IS NULL OR Field2 = @field2)
....

If any parameter passed to the stored procedure with a NULL value then the whole condition will be ignored.

Note that: I added WHERE 1 = 1 in order to make the query work in case no parameter passed to the query and in this case alll the result set will be returned, since 1 = 1 is always true.