SQL syntax for checking to see if an INSERT was successful?

Question

I'm working on a school assignment that wants me to insert some new values into a database table and then print a message based on whether or not the INSERT was successful.

The question goes like this:

Write a script that attempts to insert a new category named “Guitars” into the Categories table. If the insert is successful, the script should display this message: SUCCESS: Record was inserted.

If the update is unsuccessful, the script should display a message something like this: FAILURE: Record was not inserted. Error 2627: Violation of UNIQUE KEY constraint 'UQ_Categori_8517B2E0A87CE853'. Cannot insert duplicate key in object 'dbo.Categories'. The duplicate key value is (Guitars).

Currently, this Categories table consists of 2 columns: CategoryID and Category name. It's populated with the values

1     Guitars
2     Basses
3     Drums
4     Keyboards 

Obviously the Guitars category that the question wants you to insert is already there, so I'm guessing the whole point of the question is to get it to print the error message. The logic of the question seems fairly straightforward; insert the Guitars category into the table. If the insert was successful, print such-and-such. If it was unsuccessful, print so-and-so. I'm just not sure about the syntax. Here's the SQL code I've got so far:

USE MyGuitarShop;

INSERT INTO Categories (CategoryID, CategoryName)
VALUES (5, 'Guitars')

IF (          ) --insert is successful
    PRINT 'SUCCESS: Record was inserted'
ELSE --if insert is unsuccessful
    PRINT 'FAILURE: Record was not inserted.'
    PRINT 'Error 2627: Violation of UNIQUE KEY constraint 'UQ__Categori__8517B2E0A87CE853'.' 
    PRINT 'Cannot insert duplicate key in object 'dbo.Categories'. The duplicate key value is (Guitars).'

I feel like there'd be some sort of boolean equation in that IF statement (IF INSERT = success, IF success = TRUE, etc.) but I'm just not sure how to write it. Am I on the right track?

EDIT: I should mention I'm using SQL Server 2012

Solution 1

I would use try/catch myself

begin try
insert query
print message
end try

begin catch
print message
end catch

You should be able to take it from here.

Solution 2

USE MyGuitarShop
GO

BEGIN TRY
    -- Insert the data
    INSERT INTO Categories (CategoryName)
    VALUES ('Guitars')
    PRINT 'SUCCESS: Record was inserted.'
END TRY
BEGIN CATCH
    PRINT 'FAILURE: Record was not inserted.';
    PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER(), 1) + ': '+ ERROR_MESSAGE()
END CATCH
GO

Comments

[EJF]

Great idea! That did the trick! (and I'll bet that's what we were supposed to do in the first place because we just talked about try/catches in class last week) Thanks a bunch!

[CRAFTY DBA]

The catch will print PK violation even if there is not one. Need to check error code!! msdn.microsoft.com/en-us/library/ms175069.aspx ERROR_NUMBER()