March 17, 2010 at 12:22 pm
Hi,
I am new, self taught etc.
Im am trying to write a statement that will add a column to database and then make some updates. Although because this is going as part of an update program then it risks being run many times. I want to use the IF ELSE system, but in the case of the column not existing I get an error referring to the update part on account of the column not yet existing. If I put a 'GO' in after the add column it ruins the IF, ELSE, BEGIN, END part
if not exists (select * from syscolumns
where id=object_id('Calendar') and name='RDATE')
BEGIN
ALTER TABLE Calendar add RDATE DATETIME
UPDATE calendar set RDATE = DATE + 36163
END
ELSE
BEGIN
PRINT 'ALREADY THERE'
END
Any suggestions of the method to get around this gratefully received.
Matthew
March 17, 2010 at 12:27 pm
The update cannot occur there.
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE id=OBJECT_ID('Calendar') AND name='RDATE')
BEGIN
ALTER TABLE Calendar ADD RDATE DATETIME
END
ELSE
BEGIN
PRINT 'ALREADY THERE'
END
GO
UPDATE calendar SET RDATE = DATE + 36163
-- Add some check to make sure you want to do the update.
-- Cory
March 17, 2010 at 12:52 pm
Hide the update in an execute statement:
if not exists (select * from syscolumns
where id=object_id('Calendar') and name='RDATE')
BEGIN
ALTER TABLE Calendar add RDATE DATETIME
exec ('UPDATE calendar set RDATE = DATE + 36163')
END
ELSE
BEGIN
PRINT 'ALREADY THERE'
END
March 17, 2010 at 1:02 pm
Michael Valentine Jones (3/17/2010)
Hide the update in an execute statement:
if not exists (select * from syscolumns
where id=object_id('Calendar') and name='RDATE')
BEGIN
ALTER TABLE Calendar add RDATE DATETIME
exec ('UPDATE calendar set RDATE = DATE + 36163')
END
ELSE
BEGIN
PRINT 'ALREADY THERE'
END
Even better. 🙂
-- Cory
March 17, 2010 at 4:48 pm
Thanks for your help guys !
Matt
March 17, 2010 at 11:32 pm
Not trying to be a smart guy here...
It's normally a very bad idea to programatically add a column as a matter of course and it's usually really bad if it's accomplished by a sproc that a user may have the ability to execute. Sure, there will be administrative exceptions but, other than that, it's usually a very bad idea.
With that in mind, why are you adding a column in this way?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2010 at 7:05 am
It's normally a very bad idea to programatically add a column as a matter of course and it's usually really bad if it's accomplished by a sproc that a user may have the ability to execute.
I have done this many times, but never as a sproc - it was code that was checked in and deployed 1 or more times to various DB's, hopefully once to prod, but many times to any dev and test environments. I did not do data updates at that time however.
-- Cory
March 18, 2010 at 7:54 am
Jeff Moden (3/17/2010)
Not trying to be a smart guy here...It's normally a very bad idea to programatically add a column as a matter of course and it's usually really bad if it's accomplished by a sproc that a user may have the ability to execute. Sure, there will be administrative exceptions but, other than that, it's usually a very bad idea.
With that in mind, why are you adding a column in this way?
The problem is that a retailed application requires updates to the database structure. Currently we have engineers remotely login to make the alterations, however this is becoming unmanageable, so we need an automation.
The code doesn't appear in the main application, only the update programs.
Since there is the chance that the same code is executed twice, hence the need for the IF, ELSE stuff.
If there is a better way to do this, I'm all ears !!
Matt
March 19, 2010 at 7:47 am
We do the same thing with the application I support. We use the dynamic SQL method that Michael posted to do this in most of our scripts. One hitch that may come up is if the alter table statement passes but the update fails for whatever reason. The script can't just be rerun because the column already exists so the update won't run. This will get around that:
if not exists (select * from syscolumns
where id=object_id('Calendar') and name='RDATE')
BEGIN
ALTER TABLE Calendar add RDATE DATETIME
END
ELSE
BEGIN
PRINT 'ALREADY THERE'
END
exec ('UPDATE calendar set RDATE = DATE + 36163 WHERE RDATE IS NULL')
Of course, if NULL is a valid value after the new version has been running for a while then you may need to find another way to deal with that if the update failing is a possibility.
April 2, 2010 at 6:57 pm
m.dunster (3/18/2010)
The problem is that a retailed application requires updates to the database structure.
Thanks for the feedback, Matt, but could you explain the business reason behind this? I mean, why does the application require updates to the database structure? What does the application do where such a thing is actually necessary?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2010 at 12:37 pm
Jeff Moden (4/2/2010)
m.dunster (3/18/2010)
The problem is that a retailed application requires updates to the database structure.Thanks for the feedback, Matt, but could you explain the business reason behind this? I mean, why does the application require updates to the database structure? What does the application do where such a thing is actually necessary?
Hi Jeff,
The application is being developed quite quickly and as such requires frequent database structural change. The actual application won't be executing this code, it is left to a specific update utility.
Matt
April 5, 2010 at 7:13 am
Thanks, Matt... Whew!... I thought you were building an "retail" application that, once released, was going to be making changes to the structure of DB objects.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply