Creating a Stored Procedure which Accesses Nonexistant column

[cmgaviao]

Hello,

I am somewhat new to stored procedures. I'm trying to create SP that adds a column to a table, Accesses the column in a select statement, and then drops the column.

It seems fairly straight forward to simply add the appropriate sql to the SP. However, when I Run the Create SP, it tells me that the column does not exist, and does not create the SP. Is this not possible to do?

TIA for any suggestions...

 

[nigelrivett]

The sp resolve the ids of any objects when it runs.
It will include create table in this resolution but not alter table so when it tries to generate the query plan your column does nor exist and gives an error.

The resolution is to access the new column in a different batch - eith place the code in dynamic sql or into another SP called from tyhe first (which will be recompiled on every run).

This is a very dubious thing to do though. Static objects should be static and not changed at run time.

see

http://www.nigelrivett.net/AccessTempTablesAcrossSPs.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

What are you using this column for? A column that is created and then dropped is not a good thing to do to your data structure. Inthe first place you don;t want users to have the ability to make changes to the structure, this is a security issue. Inthe second place, what happens if multiple people are running this procedure simulatanoeusly.

If you are doing a calculatin, just put it in your select statment and give it an alias as the column name. If you want a temporary item number type of thing where you want the items form your select to be in the numbered order they are inthe select, then create a temp table with an identity field.

But adding and dropping columns to an existing table is a very bad practice from a stored procedure.

Questions about posting. See faq183-874

 

[cmgaviao]

Basically,
I am importing data into our database.
I Have a batch of statements to run and a couple of them require variables retrieved from previous statements. The reason for adding the column is so that I can add the correct data into this particular table. Once its in there, it relates back on a completely different level. But, for the purposes of the import, it's necessary to add this column.

I suppose I'll have to put these parts into a separate procedure that runs after these columns have been created.

Thanks for the ideas.

 

[ESquared]

Use a temp table with additional columns containing the correct primary keys so you can join it to the main data table.

I concur with everyone else that adding and dropping a column is a *really* bad thing to do.

It's pretty technical but you might like to see Nigel's page, Alter table - good or bad?, which ultimately demonstrates that repeated alter table commands can cause significant problems.