• I would be VERY tempted to create multiple stored procedures, one as a "parent", and one for each of the possible combinations of the @flag variables.

    Call the parent passing in the flag(s), use one or more nested case statements (or a series of IF statements, whatever) in the parent to determine which of the child procedures is to be called. If there are additional parms, other than the flags, they can be passed to the child proc as needed.

    Since each of the child procs will do only what is necessary for that flag combination, each can be optimized separately, and only those tables needing to be read will be read for any specific call. You will not have to recompile on each call, and each separate proc, being unique to that specific flag combination, can determine a good query plan for that combination.

    It does introduce more separate bits of code (i.e., procedures) to be maintained, but it also simplifies each piece, and makes it much easier when (not if) something changes or a new flag combination is needed, simply create a new SP to handle the new combination, then modify the parent to call the new proc with that flag combination.

    It may be a style thing, but I prefer a lot of simple procedures, each doing one specific thing, to one massive proc, filled with conditionals and control-of-flow. I believe it makes the code easier to understand, to document, to maintain, and to troubleshoot.