Click here to Skip to main content
13,048,846 members (76,518 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 bookmarked
Posted 24 Oct 2011

Updating a temp table in a Stored Proc

, 1 Nov 2011
Rate this:
Please Sign up or sign in to vote.
How to update the schema of a temp table in a Stored Procedure in SQL Server

Well, I just spent twenty/thirty minutes realising that when you change the definition of a temporary table being used in an SQL Server Stored Procedure, you need to actually delete the temp table before you can recompile the procedure. So, something like:

IF OBJECT_ID(N'tempdb..#tableName', N'U') IS NOT NULL DROP TABLE #tableName;
CREATE TABLE #tableName(
    Key1        INT
,   Description NVARCHAR(255)
/* do stuff with table */

And then change it to:

IF OBJECT_ID(N'tempdb..#tableName', N'U') IS NOT NULL DROP TABLE #tableName;
CREATE TABLE #tableName(
    Key1        INT
,   Key2        INT
,   Description NVARCHAR(255)
,   AggAmount   FLOAT
/* do stuff with table referencing agg amount column*/

Then the table definition won't update when you change it in code without dropping the stored definition. You can highlight the drop statement and run that by itself, and then it'll recompile your code.

Simple, but easy to miss.

Hope I saved somebody a little hair-pulling.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Mel Padden
Architect UBS AG
Switzerland Switzerland
Mel Padden:

Musician, papier maché guru, international authority on the modern crisp, and sometime software developer based in Zurich, Switzerland and Dublin, Ireland.

You may also be interested in...

Comments and Discussions

GeneralReason for my vote of 1 GEEZ. Pin
Mr President1-Nov-11 10:41
memberMr President1-Nov-11 10:41 
GeneralYou might change the title to Updating a temp table SCHEMA i... Pin
djj551-Nov-11 0:49
memberdjj551-Nov-11 0:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 1 Nov 2011
Article Copyright 2011 by Mel Padden
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid