转换内联SQL存储过程 [英] Converting inline SQL to stored procedure

问题描述

我工作在现有的ASP.NET应用程序。当前的应用程序使用了大量的在线查询。现在，他们要重写所有查询到唯一的存储过程。

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.

我的问题是，这些查询都是非常动态和查询基于不同的级联 IF ... ELSE 的条件，例如：

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.

推荐答案

转至您可能需要的所有参数，并使用的 ISNULL 和/或当条款创建相同的查询作为原始程序。

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.

编辑：还有一个理由简单地将它们全部转换为存储过程 - 当未来的开发者加入进来，想添加一些SQL，他们会遵循约定，并创建一个新的存储过程本身。我想其中一个原因你code散落着动态SQL是因为它已与动态SQL散落。也许随着时间的推移，你可以提高它们（巴掌的的遗产，必须修复的每个转换的顶部），你会得到他们解决了设计本身了。

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.

这篇关于转换内联SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！