Xedni - Thursday, February 23, 2017 12:10 PM
The only things that are 'planless' are DML statements that can only execute one way (eg CREATE TABLE). All DML has to have a plan generated in order to execute. Since SQL 2005, ad-hoc SQL and dynamic queries are treated identically to stored procedures in terms of their plans being cached.
I'm not sure why you're looking to add recompile to that process. There's nothing in it that requires a fresh plan on every execution of the loop. Generating a plan the first time and reusing it on subsequent trips through the loop looks like the more optimal approach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability