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