stored procedure probs.

[kingz2000]

Hi,

I have a problem saving a stored procedure which contains 4 sql-statements. The procedure contains, an alter table to a table, which adds a column.This is followed by an Insert statement, and then an update satement relying on the added column, and concluded by an alter table which removes the added (temporary) column.

The problem is when I try to save the procedure it dissallows it, since the column is not contained in the table.

How can I get around that?

I have tried adding the column manually, just in order to be able to save the stored procedure, but when I try to execute the statement, I get the same error messaging, alerting me that I don't have the extra column in my table.

Thanks for your help in advance!

Kingsley

 

[TysonLPrice]

That may be the compiler catching the missing column being referenced in the script even though it's added later. I don't know a way around that.

 

[tcsbiz]

When you use SELECT, INSERT, and UPDATE in a procedure, the compiler checks if the columns specified exist in the table.

Execute the INSERT/UPDATE procedure from the ALTER procedure.

I think what you may have to do is use separate procedures. One for the ALTER TABLE and another for the INSERT and UPDATE.

 

[mutley1]

Sure the experts will have a better way round it, but can you not use 2 stored procs, with 1 calling the other?

Create proc as

Alter table.......etc.......
EXEC stored_proc2
(which would contain insert / update / removing column)

Not tested as I am just leaving for the day, but HTH.

Rgds,

M

 

[kingz2000]

well, I would still need a procedure for the 'alter table add proc'..which would only work if it exists.

I guess I just have to have this ugly, temporary column in the table...damn!

 

[mutley1]

Silly question, but why not:

1. have the extra column in there
or
2. do the computation within the data extraction as you seem to be adding a column, calculating then dropping it?

If the temp column is going to be colA - colB then just put that calculation in the extraction. If it is a link to another table, just use a join?

HTH,

M

 

[kingz2000]

well, I don't exactly understand your 2nd point..

What I'm doing is adding a column;
then Insert into the table, whereby the inserted datasets are all marked using the extra column,
and then I follow on with an Update on all the marked datasets.
So I'm using the extra column only as an aid to a complex algorthm..I don't really want this temporary column in my table. Hence its label as 'ugly'.

 

[danvlas]

how about a table variable with 2 fields: the link to the main set and the 'temp' column?
You can use that in a join and get rid of the @tablevariable when procedure ends

You could also insert your results in a table var that has ALL columns from your dataset plus the extra column.

HTH


Daniel Vlas
Systems Consultant

 

[mutley1]

If you could post some data, i.e. table structure, adding of column and what you are trying to insert into the temp column then we can give you a better answer. My second point which seemed fuzzy to you was basically that you are adding something, populating it then dropping it. Why not have it as a permanent column OR do the calculation to extract the data when you are running the stored procedure to get the data out?

Rgds,

M.