Alter Table in SQL Server Transaction
I'm trying to write this transaction where I'm trying to add a column to a table using TRY..CATCH and TRANSACTION. Here's my code. But the problem is the column already exists in the table and the catch block should execute, but the catch block is not executing and the transaction is not being rolled back and also the select error_number statement is not executing.
BEGIN TRY
BEGIN TRANSACTION;
ALTER TABLE ONADJ ADD BR_INIT CHAR (3) NULL REFERENCES BRANCH(BR_INIT)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
开发者_高级运维 SELECT ERROR_NUMBER() AS ERRORNUMBER,ERROR_MESSAGE() AS ERRORMESSAGE;
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'THE TRANSACTION IS IN AN UNCOMMITTABLE STATE. ROLLING BACK TRANSACTION.'
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'THE TRANSACTION IS COMMITTABLE. COMMITTING TRANSACTION.'
ROLLBACK TRANSACTION;
END;
END CATCH
Instead of using transaction and expecting TRY CATCH to work, a better alternative would be to check if the column exists in the table and if not, then use alter query as given below -
if Not Exists(select * from sys.columns where Name = N'columnName'
and Object_ID = Object_ID(N'tableName'))
begin
-- Your alter statement
end
Instead of using sys.columns, use the Information_Schema object that is a standard across databases. The syntax is also simpler and easier to remember:
If Not Exists (Select * From Information_Schema.Columns
Where Table_Schema = 'SchemaName' -- or dbo if you aren't using schemas
And Table_Name = 'TableName'
And Column_Name = 'ColumnName'))
Begin
-- Your alter statement
End
精彩评论