Now I want that if '? How can I achieve this in SQL? Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
Collectives on Stack Overflow. Learn more. Asked 9 years, 1 month ago. Active 2 years, 8 months ago. Viewed k times. Fseee Fseee 2, 8 8 gold badges 37 37 silver badges 56 56 bronze badges. This makes me hate T-SQL even more Add a comment. Active Oldest Votes. It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search. Or will SQL Server take care of that?
I think option 1 is good as I don't have to duplicate the code, unless it's slower. The original procedure has many lines of code. So I don't want to duplicate all code just for one condition, unless it's the only good option. The problem is, Actual SP is having many lines of code - So I don't want to duplicate all code just to add one where condition, unless it's the only good option. Community wiki answer :. Assuming 0 is not a valid Age. Otherwise, you could use -1 as a default instead of 0.
This adds a small overhead on each call as the statement not whole procedure is recompiled, but it allows the parameter embedding optimization, so you will get an optimal execution plan for the specific value of MinimumAge on each execution. From my experience, the best solution is a third one, which follows the rule for 'one procedure for one task'. Your procedure currently serves 2 tasks - returning all employees, and returning a sub set based on age.
Probably not with a simplified statement like you gave, but very likely with more complex code. You can still run into parameter issues if MinimumAge values differ significantly enough to require a different plan. That can be solved too, but is a separate issue.
If the additional IO overhead is negligible then I'd go with Option 1 and just keep an eye on it. With Option 2 you're complicating the SP and opening yourself up to Parameter Sniffing issues, and that's an issue that can hit you out of the blue with no warning and lead to a miserable day at the office.
Sign up to join this community. The best answers are voted up and rise to the top. In general, properly applied parameterization can assist in security for SQL Server and can have performance implications. But, some queries cannot be fully parameterized such as when the column names, table names, or other clauses need to be added or modified dynamically. When non-parameterized dynamic SQL is used, the performance and security implications should be kept in mind.
His experience is 5 years , i need the below experience of 'RAJ'. The parameter values are added by simple text replacement before the query is sent to the server, so they can be used anywhere. Table names, database names, server names, string literals, or pieces of any of those. Parameter sniffing is expected behavior and is normally desirable.
To perhaps oversimplify, if a query is called that is not in the cache, SQL server must compile it. When it does so, it will look at or "sniff" the parameters used for that query and optimize its execution plan for those values.
The "Parameter Sniffing Problem" arises if there is a plan in the cache, but the plan was suboptimal this time because it was optimized for parameters that would return a different cardinality. Although this can be a performance problem in some situations, this problem is often well outweighed by the benefits of execution plan reuse, elegance of code, and in some situations security benefits that come from proper parameterization.
It is also worth noting that using dynamic SQL or not parameterizing your code will not always prevent the "parameter sniffing problem" because later versions of SQL Server will sometimes parameterize simple queries behindt he scenes in order to gain the benefits of query plan reuse. Now, if parameter sniffing does cause performance problems in your particular case there are a number of ways to address it. Greg Larson also has an article detailing how this problem can come up, why it often does not come up, and options on how to deal with it when it does come up that might be useful.
Text; using System. Sql; using System. Add " monthNo", System. Int ; cmd. Parameters[" monthNo"]. Add " minAmount", System. Decimal ; cmd. Parameters[" minAmount"]. ExecuteReader ; while reader.
0コメント