Dropping a Table within a Stored Procedure - errors

Question

Dropping a Table within a Stored Procedure - errors

md733406

Grasshopper

Points: 19
More actions
September 7, 2012 at 11:51 am

#280973

I have a few lines of code I want to put in a stored procedure that creates a table and makes some changes to it. The first time I run the code it works fine. However once the table already exist in the database, I include the "DROP TABLE" line to the begining of the code and I recieve unrelated(in my opinion) errors. Any ideas??? Thanks for the help!!!
When I run the first time without the DROP TABLE I get a successful execution.
When I run it after the first time now including the drop table statement I get these errors
Msg 207, Level 16, State 1, Line 14
Invalid column name 'GROUPFIELD2'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'GROUPFIELD3'.
Here is my code:
DROP TABLE dbo.Budget_PandL_Final
SELECT GROUPFIELD1 as 'Year',GROUPFIELD2,GROUPFIELD3,REPORTLINEDESCRIPTION as 'Account', TOTALCOLUMN as '2013Budget'
INTO dbo.Budget_PandL_Final
FROM dbo.PandL_Budget_Upload
ALTER TABLE dbo.Budget_PandL_Final
ADD Center nvarchar(255)
ALTER TABLE dbo.Budget_PandL_Final
ADD [Outlook_2012] nvarchar(255)
UPDATE dbo.Budget_PandL_Final
SET Center = cast ((dbo.Budget_PandL_Final.GROUPFIELD2) + <--THIS IS LINE 14--(dbo.Budget_PandL_Final.GROUPFIELD3) as nvarchar(255))
UPDATE dbo.Budget_PandL_Final
SET Outlook_2012 = 0
DELETE FROM dbo.Budget_PandL_Final
WHERE dbo.Budget_PandL_Final.[2013Budget] = 0
UPDATE dbo.Budget_PandL_Final
SET dbo.Budget_PandL_Final.Account = dbo.Crosswalk.Standard
FROM dbo.Crosswalk
FULL JOIN dbo.Budget_PandL_Final on dbo.Budget_PandL_Final.Account = dbo.Crosswalk.EPIS
DELETE FROM dbo.Budget_PandL_Final
WHERE Account is null
ALTER TABLE dbo.Budget_PandL_Final
DROP COLUMN GROUPFIELD2
ALTER TABLE dbo.Budget_PandL_Final
DROP COLUMN GROUPFIELD3

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004704 More actions · Answer 1

Since you're dropping the whole table at the beginning, why do you need a permanent table at all? Just use a temp table and include "dummy" definitions for the2 columns you're adding so you don't have to alter the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

md733406 Grasshopper Points: 19 More actions · Answer 2

I need to be able to access the table through other applications such as report writer and dundas. Dundas will not work with temp tables/views

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

You're running into behavior set forth by something in SQL Server called 'deferred name resolution.' When your proc is first compiled your final table does not yet exist so SQL Server happily compiles the proc deferring the column resolution of that table to the first time the proc is executed.

The second time you execute it your final table does exist so SQL Server no longer will defer name resolution of the columns in that table, i.e. SQL Server attempts to resolve the names of the columns in the final table against but it cannot because you have since dropped those columns from the permanent table.

Try it like this using a temp table as a scratch location yet still resulting in a permanent table. If you have any questions about anything I attempted to tighten-up post back:

IF EXISTS ( SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'dbo.Proc1')

AND type IN (N'P', N'PC') )

DROP PROCEDURE dbo.Proc1;

GO

CREATE PROC dbo.Proc1

AS

BEGIN

IF OBJECT_ID(N'tempdb..[#Budget_PandL_Final]') IS NOT NULL

DROP TABLE [#Budget_PandL_Final];

SELECT GROUPFIELD1 AS 'Year',

GROUPFIELD2,

GROUPFIELD3,

REPORTLINEDESCRIPTION AS 'Account',

TOTALCOLUMN AS '2013Budget',

CAST((GROUPFIELD2 + GROUPFIELD3) AS NVARCHAR(255)) AS Center,

CAST('0' AS NVARCHAR(255)) AS [Outlook_2012]

INTO [#Budget_PandL_Final]

FROM dbo.PandL_Budget_Upload

WHERE [2013Budget] != 0;

UPDATE b

SET Account = c.Standard

FROM dbo.Crosswalk c

FULL JOIN [#Budget_PandL_Final] b ON b.Account = c.EPIS

WHERE c.Standard IS NOT NULL;

IF OBJECT_ID(N'dbo.Budget_PandL_Final') IS NOT NULL

DROP TABLE dbo.Budget_PandL_Final;

SELECT Year,

Account,

[2013Budget],

Center,

Outlook_2012

INTO dbo.Budget_PandL_Final

FROM [#Budget_PandL_Final];

END

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

md733406 Grasshopper Points: 19 More actions · Answer 4

Thanks for the awesome answer!!! Ill give it try in a few days when I get a chance. Thanks again for the thorough explanation at least now I have some peace of mind after 4 hours of mind pain.