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!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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.
Let’s read that error message
“the stored procedure altered the schema”?
Huh, let’s take a look at what the stored procedure does.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE #HasSuffix (id int) | |
| SELECT name FROM tempdb.sys.tables WHERE name LIKE '%Suffix%' |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
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!
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.
or if it’s small results and doesn’t need to enlist in transactions you can instead cache the sproc results into a tablevar