Try Catch Can't Handle Alter Table

Why I this can't handle the alter table? Begin Try alter table nyork add [Qtr] varchar(20) End Try Begin Catch Print 'Column already exist' End Catch'

Solution 1:

Because one of them is a transact sql command (the try catch) and the other is a DDL statement.

You'd probably do better off querying to see if the column exists before doing the alter statement.

To do this with MSSQL, see How to check if a column exists in a SQL Server table?

Specifically for your case,

IF COL_LENGTH('nyork', 'Qtr') ISNULLBEGINaltertable nyork
    add [Qtr] varchar(20)
END

Solution 2:

You cannot do such a thing. TRY...CATCH can only handle runtime errors. Your script will run as long as the column does not exist but not when it is already there. The name resolution of the objects is done at compile time. Therefore SQL Server will always recognize the missing column before it starts any execution. For that reason, you can't also do such a thing with dynamic SQL.

Solution 3:

You can wrap it with exec('alter goes here'). Then catch will catch

Solution 4:

As @Marcus Vinicius Pompeu said: "You'd probably do better off querying to see if the column exists before doing the alter statement."

But, if you really want to use TRY...CATCH with DDL. There is two way for do this.

1. Use dynamic SQL in the TRY block - It has been answered here.
2. Use stored procedure in the TRY block - Based on documentation.

Example based on your code:

DROPPROCEDURE IF EXISTS dbo.sp_my_proc
GO
CREATEPROCEDURE dbo.sp_my_proc
AS--Your original code here:ALTERTABLE nyork ADD [Qtr] VARCHAR(20)
GO

BEGIN TRY  
    EXECUTE dbo.sp_my_proc 
    --OptionalDROPPROCEDURE IF EXISTS dbo.sp_my_proc
END TRY  
BEGIN CATCH  
    --Catch your error hereSELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;