I also realized that since the proc has been compiled with ANSI_NULL set to ON it should always be running under the ANSI_NULLS ON. I ran the proc from a QA window with the Connection properties set to have ANSI_NULLS OFF and it did not cause the proc cache to get flushed or recompile of the proc, because the proc had been compiled with the ANSI_NULLS ON.