开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜