Using alter command in Stored Procedure

Question

Using alter command in Stored Procedure

SQLUser-308930

Old Hand

Points: 312
More actions
July 29, 2009 at 12:59 pm

#224053

I created the following SP but when I execute it in query analyzer I receive an error saying the Edit field is an invalid column name. I know it runs thru the point of adding the Edit field because I can see it in the table but when the update statement is run it can't find the new field in the table to make the updates. Can someone give me some guidance on what I am doing wrong? Thanks!
create procedure ImportandAltersp AS
select *
into Table2
from Table1
where date > '07/01/2009'
alter table Table2
add Edit char (3)
update table2
set Edit = 'Yes'
go

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you need to either built your table with all it's columns, or use the EXEC(@sqlstatement) to do the alter an iupdate.

the database engine expects either a GO statement, so it knows the ALTER command is completed, so that it is commited to the metatadata.

the best solution build the whole thing in one pass:

select Table1.*,'Yes' As [Edit]

into Table2

from Table1

where date > '07/01/2009'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

You should not create/alter a table thru procedure. What happens when you run the procedure twice?

Madhivanan

Failing to plan is Planning to fail

Lowell SSC Guru Points: 323518 More actions · Answer 3

i assumed this was pseudocode and he was using a temp table in the procedure;if it's a real table, Madhivanan is right , you'll have concurrency issues.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bethrich SSC Veteran Points: 288 More actions · Answer 4

Here's some code I did for a data-driven conversion in SQL 2005. It should work for SQL 2000, but you won't need the section for the varchar(max).

First the stored proc:

create proc up_add_field_to_NCEM43_Data

@fld_name nvarchar(30), @fld_size int, @fld_type varchar(30)

as

begin

declare @sqlbuff nvarchar(255)

if (substring(@fld_name,1,1) = '$')

select @fld_name = substring(@fld_name,2,datalength(@fld_name)-1)

if (@fld_size > 8000)

begin

set @sqlbuff = 'Alter table NCEM43_Data '

+ 'add ['

+ @fld_name

+ '] '

+ @fld_type

+ '(max) NULL'

end

else

begin

set @sqlbuff = 'Alter table NCEM43_Data '

+ 'add ['

+ @fld_name

+ '] '

+ @fld_type

+ '('

+ convert(varchar(4),@fld_size)

+ ') NULL'

end

print @sqlbuff

exec (@sqlbuff)

if (@@error != 0)

return -1

else

return 0

end

go

Then some calling code:

declare @error_countint

DECLARE @retstatint

declare @fld_namenvarchar(30)

execute @retstat = up_add_field_to_NCEM43_Data @fld_name, 255, 'nvarchar'

if (@retstat != 0)

begin

set @error_count = 1 + @error_count

end

Of course you still will have the challenge of updating a column. My solution was to also put the update statement into a stored procedure much like the first one.

Good luck!

Beth Richards
Sybase, Oracle and MSSQL DBA

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

@sqluser...

Ummm... what is this for? I mean, what is the business reason behind adding columns to a new table? The reason why I'm asking is because if we knew the real reason behind why you are trying to do this, we just might be able to come up with a better solution than the one you're trying to solve.

--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)