SQL Server: Concatenating WHERE Clauses. Seeking Appropriate Pattern

Question

I want to take a poorly designed SQL statement that's embedded in C# code and rewrite it as a stored procedure (presumably), and am looking for an appropriate means to address the following pattern:

sql = "SELECT <whatever> FROM <table> WHERE 1=1";

if ( someCodition.HasValue )
{
    sql += " AND <some-field> = " + someCondition.Value;
}

This is a simplification. The actual statement is quite long and contains several such conditions, some of which include INNER JOIN's to other tables if the condition is present. This last part is key, otherwise I'd probably be able to solve all of them with:

WHERE <some-condition-value> IS NULL OR <some-field> = <some-condition-value>

I can think of a few possible approaches. I'm looking for the correct approach.

Edit: I don't want to perform concatenation in C#. I consider this a serious compromise to security.

Answer 1

Well you can start with

StringBuilder sb = new StringBuilder();
sb.Append("SELECT <whatever> FROM <table> WHERE  1 = 1 ");

if ( someCodition.HasValue )
{
    sb.Append(" AND <some-field> = " + someCondition.Value);
}

// And so on

Will save you the trouble of putting the first WHERE - AND

[Edit]

You can also try this

Create an SP with all required parameters for the table, and write the query like this.

DECLARE @sqlStatement NVARCHAR(MAX)

 @sqlStatement = " SELECT fields1, fields2 FROM TableA WHERE  1 = 1 "
if(@param1 IS NOT NULL)  @sqlStatement = @sqlStatement + "AND Column1 = " + @param1
if(@param2 IS NOT NULL)  @sqlStatement = @sqlStatement + "AND Column2 = " + @param2
// and so on 

sp_executeSql @sqlStatement

Also you can try similar SP but with:

SELECT fields1, fields2 FROM TableA WHERE  1 = 1 
AND ( ( @param1 IS NULL ) OR ( Column1 = @param1 ) )
AND ( ( @param2 IS NULL ) OR ( Column2 = @param2 ) )

this is definitely injection proof!

Answer 2

start with this WHERE clause:

WHERE 1=1

then append all conditions as:

AND <some-field> = " + someCondition.Value;

the optimizer will toss out the 1=1 condition and you don't have to worry about too many ANDs

EDIT based on OP's comment about not wanting to concatinate strings:

here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions

here is the table of contents:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

Answer 3

If I understand the question properly, the idea is to replace a whole section of code in C# in charge of producing, "long hand", a specific SQL statement corresponding to a list of search criteria, by a single call to a stored-procedure which would, SQL-side, use a generic template of the query aimed at handling all allowed combinations of search criteria in a uniform fashion.

In addition to the difficulty of mapping expressions evaluated on the application-side (eg. someCondition.HasValue) to expressions evaluated on the SQL-side (eg "some-condition-value"), the solution you envision may be logically/functionally equivalent to a "hand-crafted" SQL statement, but slower and more demanding of SQL resources.

Essentially, the C# code encapsulates specific knowledge about the "physical" layout of the database and its schema. It uses this info to figure-out when a particular JOIN may be required or when a particular application-level search criteria value translate to say a SQL "LIKE" rather than an "=" predictate. It may also encaspsulate business rules such as "when the ZIP code is supplied, search by that rather than by State".

You are right to attempt and decouple the data model (the way the application sees the data) from the data schema (the way it is declared and stored in SQL), but the proper mapping needs to be done somehow, somewhere.
Doing this at the level of the application, with all the expressive power of C# as opposed to say T-SQL, is not necessarily a bad thing, provided it is done
  - in a module that is independent of other features of the application
and, where practical,
  - it is somewhat data/configuration-driven as so to allow small changes in the data model (say the addition of a search criteria) to be implemented by changing a configuration file, rather than plugging this in somewhere in the middle of a long series of C# conditional statements.