SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using alter command in Stored Procedure


Using alter command in Stored Procedure

Author
Message
SQLUser-308930
SQLUser-308930
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 12
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
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72460 Visits: 40942
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
Madhivanan-208264
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 476
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
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72460 Visits: 40942
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
bethrich
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 434
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_count int
DECLARE @retstat int
declare @fld_name nvarchar(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
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216582 Visits: 41986
@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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search