Saving a Stored Procedure

[Moptop]

I am having a problem with the T-SQL parser when creating a stored proc.
The issue is that my stored proc adds a new field to a table and then updates the field. The parser fails due to the field not existing at the moment. Is there any way to bodge this to make the stored proc save.

Here is the example: (I have removed all comments, error handling, etc)

CREATE PROCEDURE dbo.DW2_tblAddress AS

ALTER TABLE imp_tblAddress ADD LookupPostCode char(8) NULL
update imp_tblAddress set LookupPostCode = 'ABC'

return(0)

GO


Gives me the error:
Server: Msg 207, Level 16, State 1, Procedure DW2_tblAddress, Line 57
Invalid column name 'LookupPostCode'.

 

[JamesLean]

First question: why would you need a procedure that adds a column to a table? Surely this is a one-time only change?

That said, if you really want to create it, just add the column to the table temporarily, create the procedure and then drop the column.

--James

 

[Moptop]

Hi
The routine is part of our Data Warehouse load where we add derived fields to tables.
Step 1 - Create new database
Step 2 - Insert all operation tables into new database
Step 3 - Add extra columns and calc rerived fields.
Step 4 - Take off clothes and dance naked around the mulberry bush
Step 5 - Start to support user's queries

 

[vongrunt]

Swap steps 3 and 4

Either don't do that within stored procedure... or exec() statement(s) that fail during name resolution.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba