StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I realize thread is older, but here are some additional details that may help in making an appropriate decision. There are multiple solutions:</p> <p>1)</p> <pre><code>SELECT ... FROM ... WHERE (T.memberType = @memberType OR @memberType = -1) AND (T.color = @color OR @color = -1) AND (T.preference = @preference OR @preference = -1) AND (T.groupNumber = @groupNumber OR @groupNumber = -1) AND (T.departmentNumber = @departmentNumber OR @departmentNumber = -1) </code></pre> <p>2)</p> <pre><code>SELECT ... FROM ... WHERE (T.memberType = @memberType OR @memberType IS NULL) AND (T.color = @color OR @color IS NULL) AND (T.preference = @preference OR @preference IS NULL) AND (T.groupNumber = @groupNumber OR @groupNumber IS NULL) AND (T.departmentNumber = @departmentNumber OR @departmentNumber IS NULL) </code></pre> <p>3) Dynamically generate the DML and use EXECUTE statement</p> <p>4) Dynamically generate the DML and use sp_executesql</p> <p>Options 1 and 2 are pretty much the same... I would tend to use IS NULL rather than -1, but as with most things, it depends on the situation. One of the disadvantages to these options is that the first execution of the stored procedure will produce a query plan that will be re-used in all subsequent calls... as the parameter's values change (specifically, which ones you want to ignore), the initial query plan may no longer be the optimal plan... to work around this problem, use the WITH RECOMPILE option (with the caveat that the procedure will be recompiled every time it is called).</p> <p>Options 3 and 4 perform better as more data is added to the table(s) and/or more criteria are added to the WHERE clause. However, these options require more effort to write the stored procedure and require more validation of the input parameters to minimize potential SQL injection vulnerability. Option 4 is better than option 3 and is somewhat simpler in a sense because your dynamically-generated SQL contains the parameter names which leads to more effective query plan reuse. Another downside to dynamically-generated SQL is that the user calling the stored procedure must have all the necessary permissions on the underlying tables/views... unless the procedure is defined with the WITH EXECUTE AS ... clause.</p> <p>In the end, I typically use dynamically generated SQL with sp_executesql to produce the best performing queries.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload