I just came across a stored procedure that does something like this:
DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (col1 INT, col2 INT, col3 INT);
SELECT @stmt = N'SELECT col1, col2, col3 INTO #foo FROM bar;';
EXEC sp_executesql @stmt;
The incorrect assumption that the developer made was that the first #foo (the one created with CREATE TABLE) was the same as the second #foo (the one referenced in the INTO clause). But that’s not the case. To prove it, try this slight variation:
DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (name NVARCHAR(2));
SELECT @stmt = N'SELECT TOP 5 name INTO #foo FROM sys.objects; SELECT * FROM #foo;';
EXEC (@stmt);
If the @stmt was really using the first #foo, then what would you see? The first two characters from name from 5 rows from sys.objects, right? No, actually you’d see Msg 8152, “String or binary data would be truncated.”. But that’s beside the point. The point is that it isn’t using that first #foo. It’s creating a second one. And it’s getting away with that because it’s running within EXEC. Take out the use of @stmt and SQL will complain to you when you try the “SELECT … INTO #foo” that #foo already exists.
Also note that the original used sp_executesql, but that my second query used only EXEC. That was to prove that it was EXEC alone, not sp_executesql, that was the differentiator.