Add WHERE clauses to SQL dynamically / programmatically

by

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.

More Related Contents:

  1. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  2. Call a stored procedure with parameter in c#
  3. Calling stored procedure with return value
  4. Passing List to SQL Stored Procedure
  5. SqlBulkCopy from a List
  6. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  7. What is passing parameters to SQL and why do I need it?
  8. How to pass a null variable to a SQL Stored Procedure from C#.net code
  9. How to give ADO.NET Parameters
  10. SQL Server Char/VarChar DateTime Error [closed]
  11. How can I add user-supplied input to an SQL statement?
  12. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  13. How to get last inserted id?
  14. C# SQL Server – Passing a list to a stored procedure
  15. What represents a double in sql server?
  16. SQL WHERE clause matching values with trailing spaces
  17. SqlConnection SqlCommand SqlDataReader IDisposable
  18. Use of SqlParameter in SQL LIKE clause not working
  19. WHERE IN (array of IDs)
  20. How can I retrieve a list of parameters from a stored procedure in SQL Server
  21. How to fill Dataset with multiple tables?
  22. SqlConnection.Close() inside using statement
  23. Using SqlDataAdapter to insert a row
  24. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  25. How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
  26. How to pass string array in SQL parameter to IN clause in SQL
  27. Check if a SQL table exists
  28. Query validation using C#
  29. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  30. How do I connect to a SQL database from C#?

Leave a Comment