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!


CREATE TABLE #temp (
id int NOT NULL
, results varchar(50) NOT NULL
);
GO
INSERT INTO #temp (id, results)
EXECUTE dbo.WhatYouGot;
GO

INSERT EXEC failed because the stored procedure altered the schema of the target table.

ErrorMessage.png
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.


IF OBJECT_ID(N'dbo.WhatYouGot', N'P') IS NOT NULL DROP PROCEDURE dbo.WhatYouGot;
GO
CREATE PROCEDURE dbo.WhatYouGot
AS
BEGIN
IF OBJECT_ID(N'tempdb.dbo.#temp', N'U') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (
id tinyint NOT NULL DEFAULT 1,
details varchar(50) NOT NULL DEFAULT 'A head full of stupid ideas :)'
);
INSERT INTO #temp DEFAULT VALUES;
SELECT id, details FROM #temp
END;
GO

view raw

WhatYouGot.sql

hosted with ❤ by GitHub

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.


CREATE TABLE #HasSuffix (id int)
SELECT name FROM tempdb.sys.tables WHERE name LIKE '%Suffix%'

TempTableSuffix
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.


CREATE TABLE #temp2 (
id int NOT NULL
, results varchar(50) NOT NULL
);
GO
INSERT INTO #temp2 (id, results)
EXECUTE dbo.WhatYouGot;
GO
SELECT id, results FROM #temp2;
GO

view raw

Temp2Works.sql

hosted with ❤ by GitHub

And we have our data!

StipidIdeas
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.

  2. or if it’s small results and doesn’t need to enlist in transactions you can instead cache the sproc results into a tablevar

Leave a Reply

%d bloggers like this: