Error in script... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error in script...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-09 : 11:13:56

Hi I am writing a stored procedure which creates a new column in a table and then removes all punctuation.

The logic of the Procedure is that the table 'TEST_TABLE' already exists and it contains a column 'NEW_COLUMN' then drop this column and create again. Once created remove punctuation from column

The following error I get is:

Error:
Invalid column name 'NEW_COLUMN'.

I have tried putting the alter and update statements in strings then executing but still get the same error.

create proc sp_test
as

DECLARE @Counter numeric,
@Max numeric,
@sqlstring varchar(500),
@PuncID numeric,
@Punctuation_Symbol varchar(10)

if exists (select * from information_schema.columns where column_name = 'NEW_COLUMN'
and Table_name = 'TEST_TABLE')
begin
alter table TEST_TABLE drop column NEW_COLUMN
end

alter table TEST_TABLE
add NEW_COLUMN nvarchar(200)

update TEST_TABLE
set NEW_COLUMN = UPPER(CompName)

SET @Max = (SELECT Count(PuncID) FROM LUT_Punctuation)

SET @Counter = 1

set @sqlstring = ''

WHILE @Counter <=@Max
begin
SET @Punctuation_Symbol = (SELECT Character FROM LUT_Punctuation WHERE @Counter = PuncID)
SET @sqlstring = 'Update TEST_TABLE set MATCHKEY_COMPNAME = replace(NEW_COLUMN, ''' + @Punctuation_Symbol + ''','''')'
exec (@sqlstring)
SET @Counter = @Counter + 1
END

Error:
Invalid column name 'NEW_COLUMN'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 11:32:59

This is because ALTER TABLE must be the last statement in the batch. So you need to do ALTER TABLE in separate script/sp and data insertion in different SP.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-09 : 12:08:33

so if i was to put:

alter table TEST_TABLE
add NEW_COLUMN nvarchar(200)

into a separate sp and call it within the sp_test, it would work?

Is there no other way?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources