使用null或默认值检查if else替代If else的最佳方法是什么？ [英] What is the best way to write where clause which is alternative for If else with the checking of null or default value ?

问题描述

当我们不想使用那么多if else语句时，我们会选择And或语句来检查商店的参数程序并显示记录。我想知道写这样的陈述的最佳做法是什么



例如



和（e .BusinessUnitId = @BusinessUnitId OR（@ BusinessUnitId IS NULL或@BusinessUnitId = 0））



i想知道这是否是正确的方式然后才能成为第一个，检查null然后进行实际的参数检查，或者还有其他任何好的语句备选方案。



请建议

解决方案

是的，这很好。订单无关紧要。但是！



对于@ BusinessUnitId = 42和@ BusinessUnitId = NULL，执行计划很可能会有所不同。我想象第一种情况下的索引搜索和另一种情况下的表扫描。由于参数嗅探错误，执行计划可以被缓存，并且您的SP将表现不佳。当你结合更多这样的条件时会变得更糟。



阅读更多关于查询计划缓存的信息，例如：在这里： http：/ /www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/ [ ^

Hi ,

When we do not want to use so much if else statements we go for "And" "or" statements which checks the parameters of the store procedure and displays the record . I want to know what is the best practice of writing such statement

For example

and (e.BusinessUnitId = @BusinessUnitId OR (@BusinessUnitId IS NULL OR @BusinessUnitId = 0))

i was wondering if this is the correct way then which is to be come first , checking of null and then actual parameter checking or there are any other good statements alternatives for this .

Please suggest

解决方案

Yes, this is just fine. The order doesn't matter. But!

Most probably the execution plan will be different for @BusinessUnitId=42 and @BusinessUnitId=NULL. I imagine index seek in the first case and a table scan in the other. Because of parameter sniffing wrong execution plan can be cached and your SP will perform poorly. It gets worse when you combine more conditions like that.

Read more about query plan caching, e.g. here: http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/[^]

这篇关于使用null或默认值检查if else替代If else的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！