Richard77 Richard77 - 1 month ago 8
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/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

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 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.

Comments