Catch exceptions with custom messages via BEGIN CATCH in TSQL-CodePudding

I try to implement T-SQL try catch in SQL Server management studio. Here is my Code:

BEGIN
BEGIN TRY
 alter table [SalesLT].[ProductModel]
 add testtimestamp123 timestamp 
END TRY
BEGIN CATCH
    RAISERROR('MY CUSTOMIZED ERROR MESSAGE',16,1)
END CATCH
END;

My expectation is, that I receive my Custom message if I run this code in sql server management studio (because I have a column with timestamp datatype in this table) but I recieve this message: A table can only have one timestamp column. Because table 'ProductModel' already has one, the column 'testtimestamp123' cannot be added.

What should I do to catch my exceptions with custom messages?

CodePudding user response：

Not all exceptions in T-SQL can be "caught". This is one of them.

If you don't want to use a "dynamic" statemnt, you could an EXISTS against the sys objects and then you can THROW your custom error if the column already exists:

CREATE TABLE dbo.SomeTable (Id int,
                            rowversion rowversion); --Don't use timestamp, it's deprecated

GO
IF NOT EXISTS (SELECT 1
               FROM sys.schemas s
                    JOIN sys.tables t ON s.schema_id = t.schema_id
                    JOIN sys.columns c ON t.object_id = c.object_id
               WHERE s.name = N'dbo'
                 AND t.name = N'SomeTable'
                 AND c.name = N'rowversion')
    ALTER TABLE dbo.SomeTable ADD rowversion rowversion;
ELSE
    THROW 65762, N'A column of the name you want to create already exists. I can''t do this. Sorry :(', 10;

GO
DROP TABLE dbo.SomeTable;

CodePudding user response：

convert that sql statement into dynamic SQL then the error will be thrown at the point of execution rather than when parsed. E.g. something like this

DECLARE @sql nvarchar(max) = 'alter table demo add testtimestamp123 timestamp ';
BEGIN TRY
    EXEC sp_executesql @sql 
END TRY
BEGIN CATCH
    RAISERROR('MY CUSTOMIZED ERROR MESSAGE',16,1)
END CATCH