I've done a bit of research and experimenting on this and I think I've worked out what's happening. I've got a theory as to why but I shouldn't take as a fact until one of the big hitters has confirmed or otherwise.
What I think is going on is that because the ProjectID column doesn't initially exist in the ##t table, the second set of @S doesn't compile even though the first statement is to DROP ##t. When SELECT * is used, the batch compiles and runs as expected.
There's a dmv that will show the metadata for the @S variable without executing it. If you run the query below it will show the compile error in the last few columns. If you change ProjectID for *, you'll see a lot more data in the dmv results.
if OBJECT_ID('temptable') is not null
drop table temptable
if OBJECT_ID('tempdb.dbo.##t') is not null
drop table ##t
create table temptable(EmployeeID varchar(50), ProjectID varchar(50))
declare @s varchar(4000)
set @s = 'SELECT top 10 T.EmployeeID into ##t FROM temptable T'
set @s = '
drop table ##t
SELECT top 10 T.EmployeeID, T.ProjectID into ##t FROM temptable T
select ProjectID from ##t
SELECT * FROM sys.dm_exec_describe_first_result_set(@s,null,0)
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537