Time to read: ~ 2 minutes
How do we get the results of a stored procedure to use later on?
We store the results in a temporary table first.
Don’t worry, that’s not the end of the post. That’s not even the point of this post. It is, however, what I was doing when I came across a weird error.
Let me show you!
INSERT EXEC failed because the stored procedure altered the schema of the target table.
Let’s read that error message
“the stored procedure altered the schema”?
Huh, let’s take a look at what the stored procedure does.
Well, look at that. We’re creating a temporary table inside the stored procedure!
So we’re not altering that table but we’re updating a table called the same thing.
Now I know that temp tables get a suffix added to them in tempdb so I thought that would be enough to distinguish them.
However, that error message means that for some strange reason, we can’t create a temp table named the same as what’s in the stored procedure; even if we drop the table first!
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
But but but but….
I thought the scope between a stored procedure and a SSMS window would be different; I mean we’re even checking if the table exists first in the stored procedure before we try and create it!
Going all the way down to the section called “CREATE and DROP, Don’t”, we read:
“the key point is that CREATE TABLE and DROP TABLE do not create and drop temporary tables in a stored procedure, if the temporary object can be cached. The temporary object is renamed to an internal form when DROP TABLE is executed, and renamed back to the same user-visible name when CREATE TABLE is encountered on the next execution.”
What to do?
Easy, we call our temporary table that stores the results something different, even as different as
And we have our data!
Encountered a problem, quick solution, and quick write up. If you encounter something like this, write it up and post it under #SQLNewBlogger.
We’d all love the knowledge share!