替代执行动态SQL [英] Alternative to executing dynamic sql

问题描述

我目前有一个与业务对象相对应的过滤器对象。该对象具有与我希望能够过滤/搜索此类业务对象列表的不同方式有关的属性。当前，这些筛选器对象具有一种方法，该方法构建一个子句的内容，然后将该子句传递到SQL Server 2000存储过程，并与其余的select查询结合起来。然后使用 Exec 执行最后一个字符串。

I currently have a 'Filter' object which corresponds to a business object. This object has properties that relate to the different ways that I want to be able to filter/search a list of such business objects. Currently these Filter objects have a method that builds the contents of a where-clause that is then passed to a SQL Server 2000 stored procedure where it is concatendated with the rest of the select query. The final string is then executed using Exec.

当前这可以正常工作，除了我担心缺少执行计划缓存的性能问题。在一些研究中，我看到了调用 sp_executesql 的用法。这是更好的解决方案还是我的工作有更好的约定？

Currently this works fine except I worry about the performance issue with the lack of execution plan caching. In some research I have seen the use of calling sp_executesql; is this a better solution or are there better conventions for what I am doing?

更新：我认为使用sp_executesql的部分问题是基于我的过滤器中的集合，我需要生成OR语句列表。我不确定是否会使用参数化查询。

Update: I think part of the issue with using sp_executesql is that based on a collection in my filter I need to generate a list of OR statements. I am not sure that the 'parameterized' query would be my solution.

示例

    var whereClause = new StringBuilder();
    if (Status.Count > 0)
    {
       whereClause.Append("(");
       foreach (OrderStatus item in Status)
       {
          whereClause.AppendFormat("Orders.Status = {0} OR ", (int)item);
       }          

       whereClause.Remove(whereClause.Length - 4, 3);
       whereClause.Append(") AND ");
    }

推荐答案

sp_executesql比exec更好，因为计划重用，并且可以使用有助于防止SQL注入的参数。如果正确使用，sp_executesql也不会导致过程缓存膨胀

sp_executesql is better than exec because of plan reuse, and you can use parameters which help against sql injection. sp_executesql also won't cause procedure cache bloat if used correctly

看看这两篇文章

< a href = http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/avoid-conversions-in-execution-plans-by- rel = nofollow noreferrer>通过使用sp_executesql避免执行计划中的转换代替Exec

如果您使用的参数不正确，将exec更改为sp_executesql不会带来任何好处

这篇关于替代执行动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！