Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using alter command in Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, July 29, 2009 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 2, 2009 7:55 AM
Points: 2, 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
Post #761906
Posted Wednesday, July 29, 2009 5:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #762035
Posted Thursday, July 30, 2009 4:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
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
Post #762255
Posted Thursday, July 30, 2009 5:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #762286
Posted Friday, July 31, 2009 10:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 7, 2014 2:08 PM
Points: 30, Visits: 433
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
Post #763268
Posted Friday, July 31, 2009 9:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:19 PM
Points: 36,995, Visits: 31,523
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #763510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse