Geek asks and answers

SQL syntax to check if INSERT was successful?

I am 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 the INSERT was successful.

The question will be like this:

Write a script that tries to insert a new category called "Guitars" into the category table. If the tab is successful, the script should display this message: SUCCESS: A record has been inserted.

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

Currently, the table of these categories consists of 2 columns: CategoryID and Category name. It is populated with values

1     Guitars
2     Basses
3     Drums
4     Keyboards

Obviously, the category of guitars that you want to insert in the question already exists, so I assume that the whole point of the question is to get it to print an error message. The logic of the question seems pretty simple; insert the category of guitars into the table. If the insert was successful, print one. If this was unsuccessful, type so-so and so. I'm just not sure about the syntax. Here is the SQL code that I still have:

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 that there will be some kind of Boolean equation in this IF expression (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 have to mention that I am using SQL Server 2012

+3
4

try/catch

begin try
insert query
print message
end try

begin catch
print message
end catch

.

+4
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
+1

.

@@ERROR . BEGIN/END TRY BEGIN/END CATCH.

, RAISERROR THROW , -.

. .

http://craftydba.com/?tag=error-handling

, , IF ELSE.

-- Select the old database
USE MyGuitarShop;
go

-- Grab error value
DECLARE @ERR INT = 0;

-- Insert the data
INSERT INTO Categories 
  (CategoryID, CategoryName)
VALUES 
  (5, 'Guitars');

-- Simple error handling
SET @ERR = @@ERROR;
IF (@ERR = 0)
  BEGIN
    PRINT 'SUCCESS: Record was inserted';
  END
ELSE IF (@ERR = 2627)
  BEGIN
    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 ';
  END
ELSE
  BEGIN
    PRINT 'FAILURE: Record was not inserted.';
    PRINT 'Error ' + STR(@ERR, 6, 0) + ' was not gracefully handled';
  END
GO
0
source

More articles:


All Articles