Temp Table Caching And Reuse

Shane O’Neill ran into an error with his stored procedure call:

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.

He wasn’t really trying to alter the schema of that temp table, but read on to see the context and solution.

Related Posts

Using Temp Stored Procedures

Bert Wagner shows how you can perform testing with temporary stored procedures: A while back I learned that it’s possible to create temporary stored procedures in SQL Server. I never put that knowledge into practice however because I struggled to think of a good use case for when a temporary stored procedure would be preferable […]

Read More

Stored Procedure IF Branching and Performance

Erik Darling explains that the IF block in a stored procedure won’t help you with performance: Making plan choices with IF branches like this plain doesn’t work.The optimizer compiles a plan for both branches based on the initial compile value.What you end up with is a stored proc that doesn’t do what it’s supposed to […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031