Exception handling in SQL Server-CodePudding

I am trying to insert a note into a table xyz. I get an error. There is another column noteid (identity) which is not specified in the list of columns, but value is being entered in values which I did on purpose.

I added 4 values to make the insert fail and check. When it succeeds, I am getting message from if statements as success, but when it fails, I am not getting any message from else statements.

Please point out my mistake, what did I do wrong ?

DECLARE @errornumbetr tinyint

BEGIN TRAN

INSERT INTO [dbo].xyz ([MemberID], [UserID], [Added])
VALUES (1, '0005135', 'USER', GETDATE())
       
SET @errornumber = @@ERROR

IF (@errornumber = 0)
BEGIN
    PRINT 'Success! Inserted note'
    COMMIT TRAN
END
ELSE
BEGIN
    PRINT 'Couldn''t insert note'
    ROLLBACK TRAN
END
GO

CodePudding user response：

Couple of things here:

Providing an example of the object you're working with would be helpful. This allows us to see what problems may be occurring due to that (data types and the like).

You can easily provide this example like this:

DECLARE @xyz TABLE (MemberID INT, UserID INT, Added DATETIME)

Next when using transactions it's a good idea to NAME them:

BEGIN TRAN InsertIntoXYZ

You can then reference them by this name when you need to.

The error you're causing to happen is a parsing problem, your query won't actually attempt to execute, because the engine can see it's wrong before anything actually happens.

We can cause other errors that will attempt to execute to trigger your condition though, such as inserting the wrong data type into a column.

Finally, TRY/CATCH is the proper way to handle this. Using this will also make available a number of other values which can provide you with more information.

Here is how I would write (and test) this:

DECLARE @xyz TABLE (MemberID INT, UserID INT, Added DATETIME)

BEGIN TRAN InsertIntoXYZ
BEGIN TRY
 INSERT INTO @xyz (MemberID, UserID, Added) VALUES
 (1 'A', '0005135', GETDATE())
 PRINT 'Success!!  Inserted note'
 COMMIT TRAN InsertIntoXYZ
END TRY
BEGIN CATCH
 PRINT 'Couldn''t insert note: '   ERROR_MESSAGE()
 ROLLBACK TRAN InsertIntoXYZ
END CATCH

(0 rows affected)
Couldn't insert note: Conversion failed when converting the varchar value 'A' to data type int.