Click here to Skip to main content
Click here to Skip to main content

Tagged as

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.

License

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

Share

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.
 
http://www.linkedin.com/in/melpadden
Follow on   Twitter

Comments and Discussions

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

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140823.1 | Last Updated 1 Nov 2011
Article Copyright 2011 by Mel Padden
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid