SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SP problem...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 12/06/2006 :  08:25:30  Show Profile  Reply with Quote
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 - 12/06/2006 :  08:30:32  Show Profile  Reply with Quote
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
Go to Top of Page

mchohan
Starting Member

39 Posts

Posted - 12/06/2006 :  08:32:29  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/06/2006 :  08:44:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 12/06/2006 :  08:46:01  Show Profile  Reply with Quote
That doesn't drop the column if it exists or doesn't update the column once created. Any ideas how to do this?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/06/2006 :  08:55:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why doesn't the column already exist?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 12/06/2006 :  08:59:39  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/06/2006 :  09:05:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 12/06/2006 09:08:29
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000