Is there a way to defer the build of the stored procedure execution plan?

(At first glance, this might look like a duplicate of the Different execution plans when executing the statement directly and from a stored procedure, or Why is the SqlServer optimizer so confused with parameters? But my actual question is slightly different)

Ok, that got me stuck for a few hours. My example is ridiculously abstracted here, so I doubt it can be recreated locally, but it does provide context for my question (Also, I'm running SQL Server 2005).

I have a basically two step stored procedure that creates a temporary table, populates it with very small rows, and then queries a very large table that joins to that temporary table. It has several parameters, but the most relevant is datetime

" @MinDate

." Essentially:

create table #smallTable (ID int)

insert into #smallTable
select (a very small number of rows from some other table)

select * from aGiantTable
inner join #smallTable on #smallTable.ID = aGiantTable.ID
inner join anotherTable on anotherTable.GiantID = aGiantTable.ID
where aGiantTable.SomeDateField > @MinDate

      

        
        
        
      

    

If I just executed this as a normal query, declaring it @MinDate

as a local variable and executing it, it creates an optimal execution plan that is very fast (attaches to #smallTable first and then only takes into account a very small subset of rows from aGiantTable when doing other operations) ... #SmallTable seems to be tiny, so it would be efficient to start with it. It's good.

However, if I make this stored procedure with @MinDate

as a parameter, it creates a completely inefficient execution plan. (I rewrite it every time so this is not a bad plan in the cache ... at least I hope it is not)

But here's where it gets weird. If I change proc to the following:

declare @LocalMinDate datetime
set @LocalMinDate = @MinDate --where @MinDate is still a parameter

create table #smallTable (ID int)

insert into #smallTable
select (a very small number of rows from some other table)

select * from aGiantTable
inner join #smallTable on #smallTable.ID = aGiantTable.ID
inner join anotherTable on anotherTable.GiantID = aGiantTable.ID
where aGiantTable.SomeDateField > @LocalMinDate

      

        
        
        
      

    

Then he gives me an effective plan!

So my theory is this: when executed as a simple query (not as a stored procedure), it waits to build an execution plan for an expensive query until the last minute, so the query optimizer knows the #smallTable is small and uses this information to provide an efficient plan.

But when executed as a stored procedure, it creates the entire execution plan at once, so it cannot use that bit of information to optimize the plan.

But why does using locally declared variables change this? Why is this delaying the creation of the execution plan? Is this actually happening? If so, is there a way to force compilation to defer (if it really does) even if there are no local variables in this way?

More generally, does anyone have any sources for when an execution plan is generated for each step of a stored procedure? Googling didn't provide any useful information, but I don't think I was looking for the right thing. Or is my theory completely unfounded?

Edit . After posting, I learned about parametric sniffing and my guess is that this is what causes the execution plan to compile prematurely (unless the stored procedures actually compile right away), so my question remains - can you force the delay? Or disable sniffing entirely?

The question is academic, since how can I force a more efficient plan by replacing select * from aGiantTable

with

select * from (select * from aGiantTable where ID in (select ID from #smallTable)) as aGiantTable

      

        
        
        
      

    

Or just suck it and mask the parameters, but nevertheless this inconsistency is very curious to me.

TL; DNR

This is a very egregious question, so in short:

Is the complete execution plan generated when the stored procedure is first called or executed? That is, if the stored procedure consists of multiple steps, is the execution plan for each step generated the first time the procedure is called, or is it only created after the previous steps have completed execution (again, on the first call)?