Converting inline SQL to stored procedure

Question

I'm working on an existing ASP.NET application. The current application uses a lot of inline queries. Now they want to rewrite all the queries into stored procedures only.

My problem is, these queries are very "dynamic" and the queries are concatenated based on different if...else conditions, for example:

string query = "Select * from EmpTable WHERE EmpType ='ACTIVE'";

if (conditionA == true)
query += "AND ID = 12345 ";

if (conditionB == true)
query += "AND Dept = 'Finance' ";

else
query += "AND Dept <> 'Finance' ";

if (conditionC == true)
query += "Order by EmpID";

else if (ConditionD == true)
query += "Order by Dept";

They also want to avoid using dynamic query. What are my options?

Edited: I know I can also build dynamic query using stored procedures, I am just wondering what are some other "less pain" options out there.

Answer 1

Pass in all the parameters you might need, and use the IsNull and/or When clauses to create the same query as in the original program.

Alternatively you can simply build the query dynamically in the sproc itself or simply create a query for each permutation. Not necessarily fun, or clever but works and makes things easier to maintain in the future - especially when you can use it as a stepping stone for future refactoring of the sprocs.

Edit: there is one more reason to simply convert them all to sprocs - when future devs come along and want to add some SQL, they'll follow the convention and create a new sproc themselves. I imagine one reason your code is littered with dynamic SQL is because it is already littered with dynamic SQL. Maybe over time you can improve them (slap legacy, must fix at the top of each convert) and you'll get them to fix up the design themselves too.