April 11, 2008 at 1:12 am
Hello,
I have a problem with the if-clause.
In the if-clause I must use a "go" to finish a batch, so that the new column is available in the next sql-statement.
But unfortunately in the if-clause the "go" seems not to be guilty.
Has aynone an idea?
Thanks
Daniel
if (select nDbSubVersion from tblVersion) = 113
begin
begin transaction
alter table tblCustAttrDefValue
add strHelpValue varchar(255) null
go
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
update tblVersion set nDbSubVersion = 114
commit transaction
end
if (select nDbSubVersion from tblVersion) <> 114
begin
rollback transaction
end
April 11, 2008 at 1:38 am
As you've discovered you cannot use a go statement inside of an if statement because the go is meant to seperate batches. So you'd have to do something like this:
begin tran
if (select nDbSubVersion from tblVersion) = 113
begin
alter table tblCustAttrDefValue
add strHelpValue varchar(255) null
end
go
if exists(select * from sys.columns where object_name(object_id) = 'tblCustAttrDefValue' and name = 'strHelpValue')
and (select nDbSubVersion from tblVersion) = 113
begin
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
update tblVersion set nDbSubVersion = 114
end
go
if (select nDbSubVersion from tblVersion) <> 114
if @@trancount > 1 rollback tran
commit tran
I haven't checked that it works but that is more or less what you'd be aiming for
April 11, 2008 at 3:40 am
You can try to run your statements as string with the exec statement like
if (select nDbSubVersion from tblVersion) = 113
begin
begin transaction
exec ('alter table tblCustAttrDefValue add strHelpValue varchar(255) null')
exec ('update tblCustAttrDefValue set strHelpValue = RTrim(LTrim(strValue))')
update tblVersion set nDbSubVersion = 114
commit transaction
end
if (select nDbSubVersion from tblVersion) <> 114
begin
rollback transaction
end
Remark: this Code is not tested.
wole
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply