Richard77 Richard77 - 1 year ago 79
C# Question

TSQL: use CASE in the WHERE clause?

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(@empType = 'salary')
//query statement here...
//same query statement here with the extra logic that C# code adds...

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 Source

Something like

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

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

Dynamically building SQL is bad for a few reasons (cant 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download