SP problem... - 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 2000 Forums

Transact-SQL (2000)

SP problem...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-06 : 08:25:30

Hi I am creating an SP and would like to drop a field (if exists), create a field and populate the field.

i.e.

CREATE PROC SP_CLEAN_COMPANY_WORDS
as

--Drops MATCH_Supplier_Name column if exists
IF EXISTS (SELECT MATCH_Supplier_Name FROM SUPPLIER_TABLE)
BEGIN
ALTER TABLE SUPPLIER_TABLE
DROP COLUMN MATCH_Supplier_Name
END

ALTER TABLE SUPPLIER_TABLE
add MATCH_Supplier_Name varchar(200)

UPDATE SUPPLIER_TABLE
SET MATCH_Supplier_Name = UPPER(Supplier_Name)

print 'test'

END

I get an error message:

Invalid Column name 'MATCH_Supplier_Name'

Any ideas?

Thanks in advance.

mchohan
Starting Member

39 Posts

Posted - 2006-12-06 : 08:30:32

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE [NAME] = 'TRN_Course' and XTYPE = 'U')
BEGIN
IF EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE [NAME] = 'TRN_Course' AND XTYPE = 'U') AND [MY] = 'courseid')

See if the above example helps, i did this a while ago

mchohan
Starting Member

39 Posts

Posted - 2006-12-06 : 08:32:29

Sorry, thats confusing. see belwo for what i was doing. you should be able to adapt it to yours. Thanks.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE [NAME] = 'TRN_Course' and XTYPE = 'U')
BEGIN
IF NOT EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE [NAME] = 'TRN_Course' AND XTYPE = 'U') AND [NAME] = 'courseid')
BEGIN
ALTER TABLE [dbo].[TRN_Course] ADD [courseid] [int] IDENTITY(1,1) NOT NULL
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 08:44:46

Why do you drop the column and add it again? just re-update it!

CREATE PROC SP_CLEAN_COMPANY_WORDS
as

UPDATE SUPPLIER_TABLE
SET MATCH_Supplier_Name = UPPER(Supplier_Name)

print 'test'

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-06 : 08:46:01

That doesn't drop the column if it exists or doesn't update the column once created. Any ideas how to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 08:55:23

Why doesn't the column already exist?

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-06 : 08:59:39

The column may or may not exist. If the column does exist then I can do the update statement, if it doesn't I need to create it and do the update statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 09:05:36

I don't get it.

if not exists (select * from information_schema.columns where column_name = 'MATCH_Supplier_Name' and table_name = 'SUPPLIER_TABLE')
	ALTER TABLE SUPPLIER_TABLE add MATCH_Supplier_Name varchar(200)
go

UPDATE SUPPLIER_TABLE
SET MATCH_Supplier_Name = UPPER(Supplier_Name)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources