You administer a Microsoft SQL Server database named AdventureWorks2008R2. The database has a table that has the following definition:
CREATE TABLE Production.Location(
Name nvarchar(100) NOT NULL PRIMARY KEY,
StartDate datetime2 NOT NULL CHECK (StartDate >= ‘2011-01-01’))
You plan to implement custom error handling for INSERT commands. The error number for a duplicate key is 2627. The error number for a NULL violation is 515.
You need to ensure that an INSERT statement meets the following requirements:
•If a duplicate row is detected during insertion, no error message must be raised.
•For NULL errors, the prefix "NULL:" must be added to the message; all others errors must begin with "OTHER:".
•Return all errors as error number 50000.
Which Transact-SQL statement should you use?
A. INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-18′)
IF @@error = 515
RAISERROR (‘NULL:’ + Error_Message(),16,1)
B. DECLARE @msg nvarchar(2000)
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-18′)
IF @@error = 515
BEGIN
SET @msg = ‘NULL:’ + Error_Message()
RAISERROR (@msg,16,1)
END
ELSE
BEGIN
SET @msg = ‘OTHER:’ + Error_Message()
RAISERROR (@msg,16,1)
END
C. BEGIN TRY
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
BEGIN
DECLARE @msg nvarchar(2000)
SET @msg = case ERROR_NUMBER() WHEN 515 THEN ‘NULL:’ ELSE ‘OTHER:’ END + ERROR_MESSAGE()
RAISERROR (@msg,16,1)
END
END CATCH
D. BEGIN TRY
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)
END TRY
BEGIN CATCH
DECLARE @msg nvarchar(2000)
SET @msg = case ERROR_NUMBER() WHEN 515 THEN ‘NULL:’ ELSE ‘OTHER:’ END + ERROR_MESSAGE()
RAISERROR (@msg,16,1)
END CATCH
E. BEGIN TRY
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
BEGIN
DECLARE @msg nvarchar(2000)
SET @msg = case ERROR_NUMBER() WHEN 515 THEN ‘NULL:’ ELSE ‘OTHER:’ END + ERROR_MESSAGE()
RAISERROR (@msg,16,1)
END
END CATCH
F. BEGIN TRY
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2001-10-10′)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
BEGIN
DECLARE @msg nvarchar(2000)
SET @msg = case ERROR_NUMBER() WHEN 515 THEN ‘NULL:’ ELSE ‘OTHER:’ END + ERROR_MESSAGE()
RAISERROR 50010 @msg
END
END CATCH
G. BEGIN TRY
INSERT Production.Location (Name,StartDate)
SELECT ‘Paint’,’2011-10-10′ WHERE NOT EXISTS (SELECT * FROM Production.Location as L WHERE L.Name = ‘Paint’)
END TRY
BEGIN CATCH
DECLARE @msg nvarchar(2000)
SET @msg = case ERROR_NUMBER() WHEN 515 THEN ‘NULL:’ ELSE ‘OTHER:’ END + ERROR_MESSAGE()
RAISERROR (@msg,16,1)
END CATCH
H. BEGIN TRY
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
BEGIN
DELETE FROM Production.Location WHERE Name = ‘Paint’
INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)
END
ELSE
BEGIN
DECLARE @msg nvarchar(2000)
SET @msg = case ERROR_NUMBER() WHEN 515 THEN ‘NULL:’ ELSE ‘OTHER:’ END + ERROR_MESSAGE()
RAISERROR (@msg,16,1)
END
END CATCH
