TSQL: use CASE in the WHERE clause?

Question

Is there an efficient way of writing this c# codes in t-sql?

string sqlText = "SELECT col1, col2, col3 "
               + "FROM table "
               + "WHERE col1 = @val1 ";

if(condition) // i.e. if type == 'salary'
{
  sqlText += " AND col2 = @val2";
}

sqlText += " ORDER BY col1";

What I've done and it's kind of working is to just wap the whole statement by a if/being/end.

IF(@empType = 'salary')
  BEGIN
     //query statement here...
  END
 ELSE 
  BEGIN
   //same query statement here with the extra logic that C# code adds...
  END

The query was used to display data on HTML page. Now, it's been decided to convert the page to SSRS report.

Thanks for helping

Answer 1

Something like

SELECT col1, col2, col3
FROM table 
WHERE col1 = @val1
  AND (@empType <> 'salary' 
     OR (@empType = 'salary' AND col2 = @val2))
ORDER BY col1

but I'm not sure what empType is, if it is a param or something else.

Dynamically building SQL is bad for a few reasons (cannot be optimized reliably by SQL server, more exposure to potential SqlInjection), but a pattern of competing conditions like the above would work.

Consider separate stored procs and calling the correct one via code instead of this kind of mess.