![with recompile stored procedure with recompile stored procedure](http://sqlcontent.com/wp-content/uploads/2012/11/110812_1438_recompilein12.png)
This approach of only adding clauses for parameters that are actually supplied protects you from caching plans based on different sets of parameters (for example, if I supply on first execution, the seek plan on that column that gets cached isn't going to help when I ask for LIKE N'%s%'). PRINT sys.sp_executesql VARCHAR(20), VARCHAR(50), INT. SET += N' AND verifier_id = + N' OPTION (RECOMPILE) ' SET += N' AND instance_name = IS NOT NULL You didn't show the rest of your code, only that you were already using the local variables trick, but it will essentially look like this: DECLARE NVARCHAR(MAX) = N'SELECT. (For a whole lot more on this topic, see this great post by Paul White.)Īlso, if many parameters are optional (so the query has things like WHERE col = or IS NULL), this is what I call "the kitchen sink" - sometimes dynamic SQL can be a much more effective solution.
#With recompile stored procedure code#
And I wouldn't recommend the local variables "trick" - it just makes the code messier better to use OPTIMIZE FOR UNKNOWN on modern versions if that's the method that works best in your scenario.
![with recompile stored procedure with recompile stored procedure](http://2.bp.blogspot.com/-BCxGAQRIwXw/WnU_UFHOdDI/AAAAAAAAP0M/bx5V2PSGFgAZtVidQojHNhnKbJBaASUyACK4BGAYYCw/s1600/31_Create%2BStored%2BProcedure_up_Parameter_OPTION_RECOMPILE_MultiSQL.png)
You'll want to use OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure. Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others.