参数化存储过程 [英] Parameterised stored proc

问题描述

我正在编写一个参数化的存储过程.我知道您可以设置参数值，以便在执行命令中未提及该参数时显示所有结果.但我不记得是如何实现的.非常感谢任何帮助...请...

I'm writing a parameterized stored proc. I know that you can set the parameter value such that it displays all the results when the parameter is not mentioned in the execute command.. But i'm unable to recall how that is achieved. Any help is highly appreciated... Please..

推荐答案

你可以让你的 WHERE 条件像这样:

You can make your WHERE conditions like this:

WHERE (@myParam IS NULL OR @myParam = someValue)

您可以在 sproc 中使用 OPTION (RECOMPILE) 是 SQL2008SP1+(或类似的，不知道其他选项)，这取决于您的 RDBMS，以使其具有高性能.

You may be able to use OPTION (RECOMPILE) is SQL2008SP1+ (or similar, don't know other options) in the sproc, depending on your RDBMS, to get this to be performant.

来自 Erland Sommarskog 的方法:
http://www.sommarskog.se/dyn-search-2008.html#static

Method from Erland Sommarskog:
http://www.sommarskog.se/dyn-search-2008.html#static

来自链接:所有@x IS NULL 子句的效果是，如果该输入参数为 NULL，则该 AND 条件始终为真.因此，唯一有效的条件是搜索参数具有非 NULL 值的条件.

From the link: "The effect of all the @x IS NULL clauses is that if that input parameter is NULL, then that AND-condition is always true. Thus, the only conditions that are in effect are those where the search parameter has a non-NULL value.

就可维护性而言，很难为手头的搜索条件想出更好的解决方案.它结构紧凑，易于阅读和扩展.和性能?只要包含查询提示选项(重新编译)就很好.此提示强制每次都重新编译查询，在这种情况下，SQL Server 将使用实际变量值，就好像它们是常量一样."

As far as maintainability goes, it's difficult to think of a better solution for the search conditions at hand. It's compact, easy to read and to extend. And performance? Very good as long as you include the query hint OPTION (RECOMPILE). This hint forces the query to be recompiled each time, in which case SQL Server will use the actual variable values as if they were constants."

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