When Stored Procedures say they Alter Schemas of the Target Table

…even when they shouldn’t!

Words: 423
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.

When DML meets DDL…

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.

Apparently this might not mean it’s my 4th one…

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!

Checking the docs for temp tables seems to back this up

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!

Googling online for an answer leads to an article by Paul White ( blog | twitter ) about “Temporary Tables in Stored Procedures“.

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 #temp2.

And we have our data!

I shouldn’t smile about that…

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!

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

3 thoughts on “When Stored Procedures say they Alter Schemas of the Target Table”

  1. Loved your Article. 🙂 Adding my 2 Cents here.

    So the Rule of Thumb to avoid this Error “INSERT EXEC failed because the stored procedure altered the schema of the target table.” is:

    If your stored proc “SP_INNER” will someday be part of a nested stored proc i.e. if another Stored Proc “SP_OUTER” will call “SP_INNER”, then make sure you name #TmpTables in your “SP_INNER” very Unique.

    Liked by 1 person

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s