The language is: T-SQL
The goal is to create a stored procedure that uses TRY/CATCH and error handling functions. The stored procedure must be named MyOrdersInsert, it will insert into the dbo.MyOrders table. Use the following drop and create statements supplied here to create the table.
DROP TABLE IF EXISTS dbo.MyOrders;
GO
CREATE TABLE dbo.MyOrders
(
MyOrdersId INT NOT NULL IDENTITY PRIMARY KEY
,OrderDate DATETIME NOT NULL CHECK (GETDATE() >= OrderDate)
,CustomerName VARCHAR(100) NOT NULL
);
GO
In addition, you will need to create an error log table. Use the following statements in your script to create the ErrorLog table.
DROP TABLE IF EXISTS dbo.ErrorLog;
GO
CREATE TABLE dbo.ErrorLog
(
ErrorLogId INT NOT NULL IDENTITY PRIMARY KEY
,ErrorDateTime DATETIME NOT NULL
,ErrorNumber INT NOT NULL
,ErrorLocation VARCHAR(MAX)
,ErrorMessage VARCHAR(MAX) NOT NULL
);
GO
*Now write a stored proc called dbo.MyOrderInsert. The proc takes two input parameters and one output parameter. It also returns an integer value. The first input parameter is for the orderdate and must be the DATETIME data type. The second input parameter is for the CustomerName and must be of the VARCHAR(100) data type. The third parameter is an output parameter and is an integer datatype. This third parameter will contain the new OrderId if the procedure is successful. Use the SCOPE_IDENTITY() function to get the most recent value of a newly inserted row.
The integer return value will be zero if the procedure was successful. If the procedure fails, then the return value must be the error number representing the cause of failure.
If the procedure fails, it must log failure information in the ErrorLog table. Each value must be populated using the newer style error functions (not the @@ kind of functions). The exception is for the ErrorDateTime column, which must use the get date function to for its value.
Important: I will test your application with the following script
-- Used for every execution
SET NOCOUNT ON;
DECLARE @RC INT;
DECLARE @NewOrderId INT;
-- Execute with future date. Generate a 547 error
DECLARE @FutureDate DATETIME = GETDATE()+1;
EXEC @RC = dbo.MyOrdersInsert @FutureDate, 'E.F. Codd', @NewOrderId OUTPUT;
PRINT '#1 Exec with FutureDate return code = ' + CAST(@RC AS VARCHAR(10))
+ ' and new OrderId of ' + CAST(@NewOrderId AS VARCHAR(10));
-- Execute with null CustomerName. Generate a 515 error
DECLARE @NULLName VARCHAR(100) = NULL;
DECLARE @CurrentDate DATETIME = GETDATE();
EXEC @RC = dbo.MyOrdersInsert @CurrentDate, @NULLName, @NewOrderId OUTPUT;
PRINT '#2 Exec with NULLName return code = ' + CAST(@RC AS VARCHAR(10))
+ ' and new OrderId of ' + CAST(@NewOrderId AS VARCHAR(10));
-- Execute with success. Return should be 3
EXEC @RC = dbo.MyOrdersInsert @CurrentDate, 'E.F. Codd', @NewOrderId OUTPUT;
Print '#3 Exec with good data return code = ' + CAST(@RC AS VARCHAR(10))
+ ' and new OrderId of ' + CAST(@NewOrderId AS VARCHAR(10));
SELECT * FROM dbo.ErrorLog;
SELECT * FROM dbo.MyOrders;
The procedure must produce the following results (with different ErrorDateTime values). Notice there are no row counts displayed
The Northwind Database diagrams are shown below
HEH Results ErrorLogld Error Number Messages ErrorDate Time 2017-04-11 10:08:18.310 2017-04-11 10:08:18.310 1 1 547 ErrorLocation MyOrders Insert MyOrders Insert Error Message The INSERT statement conflicted with the CHECK C... Cannot insert the value NULL into column Customer... 2 2 515 Order Date My Ordersld 3 CustomerName E.F. Codd 1 2017-04-11 10:08:18.310 Results Messages #1 Exec with FutureDate return code = 547 and new OrderId of 0 #2 Exec with NULLName return code = 515 and new OrderId of O #3 Exec with good data return code = 0 and new OrderId of 3 Condensed Type Allow Nulls Identity int Order Details Column Name Condensed Type Allow Nulls Identity 8 OrderlD int 9 ProductID int UnitPrice money Quantity smallint Discount real nvarchar(40) Categories Column Name Condensed Type Allow Nulls Identity 9 CategoryID int CategoryName nvarchar(15) Description ntext Picture image int 000000 Products Column Name 8 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrder ReorderLevel 00000 int nvarchar(20) money smallint smallint smallint 00000000000 8 Identity Discontinued bit Employee Territories Column Name Condensed Type Allow Nulls Identity 9 Employeeld TerritoryID nvarchar(20) int Customers Column Name Condensed Type Allow Nulls 9 CustomerlD nchar(5) CompanyNa... nvarchar(40) Contact Name nvarchar(30) Contact Title nvarchar(30) Address nvarchar(60) M City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) OOO |TOO Territories Column Name Condensed Type Allow Nulls Allow Nulls Identity 8 TerritoryID nvarchar(20) TerritoryDescription nchar(50) RegionID int Suppliers Column Name Condensed Type Allow Nulls Identity Suppliers int CompanyName nvarchar(40) ContactName nvarchar(30) Contact Title nvarchar(30) M Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) M Fax nvarchar(24) HomePage ntext 000 Region Column Name Condensed Type Allow Nulls Identity 9 RegionID int RegionDescription nchar(50) 000 CustomerCustomerDemo Column Name Condensed Type Allow Nulls Identity 9 CustomerlD nchar(5) CustomerTypeID nchar(10) Employees Column Name Condensed Type Allow Nulls Identity Employeeld int LastName nvarchar(20) FirstName nvarchar(10) Title nvarchar(30) TitleOfCourtesy nvarchar(25) BirthDate datetime HireDate datetime Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) M Country nvarchar(15) HomePhone nvarchar(24) Extension nvarchar(4) Photo image OOOOOOOOOOOOOOOOOOO Orders Column Name Condensed Type Allow Nulls Identity od 9 OrderD int CustomerlD nchar(5) Employeeld int OrderDate datetime RequiredDate datetime ShippedDate datetime ShipVia int Freight money ShipName nvarchar(40) ShipAddress nvarchar(60) Ship City nvarchar(15) Ship Region nvarchar(15) ShipPostalCode nvarchar(10) Ship Country nvarchar(15) D Customer Demographics Column Name Condensed Type Allow Nulls Identity CustomerTypeID nchar(10) CustomerDesc ntext 000 ntext Notes Reports To PhotoPath int nvarchar(255) Shippers Column Name Condensed Type Allow Nulls Identity 8 ShipperID int CompanyName nvarchar(40) Phone nvarchar(24) HEH Results ErrorLogld Error Number Messages ErrorDate Time 2017-04-11 10:08:18.310 2017-04-11 10:08:18.310 1 1 547 ErrorLocation MyOrders Insert MyOrders Insert Error Message The INSERT statement conflicted with the CHECK C... Cannot insert the value NULL into column Customer... 2 2 515 Order Date My Ordersld 3 CustomerName E.F. Codd 1 2017-04-11 10:08:18.310 Results Messages #1 Exec with FutureDate return code = 547 and new OrderId of 0 #2 Exec with NULLName return code = 515 and new OrderId of O #3 Exec with good data return code = 0 and new OrderId of 3 Condensed Type Allow Nulls Identity int Order Details Column Name Condensed Type Allow Nulls Identity 8 OrderlD int 9 ProductID int UnitPrice money Quantity smallint Discount real nvarchar(40) Categories Column Name Condensed Type Allow Nulls Identity 9 CategoryID int CategoryName nvarchar(15) Description ntext Picture image int 000000 Products Column Name 8 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrder ReorderLevel 00000 int nvarchar(20) money smallint smallint smallint 00000000000 8 Identity Discontinued bit Employee Territories Column Name Condensed Type Allow Nulls Identity 9 Employeeld TerritoryID nvarchar(20) int Customers Column Name Condensed Type Allow Nulls 9 CustomerlD nchar(5) CompanyNa... nvarchar(40) Contact Name nvarchar(30) Contact Title nvarchar(30) Address nvarchar(60) M City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) OOO |TOO Territories Column Name Condensed Type Allow Nulls Allow Nulls Identity 8 TerritoryID nvarchar(20) TerritoryDescription nchar(50) RegionID int Suppliers Column Name Condensed Type Allow Nulls Identity Suppliers int CompanyName nvarchar(40) ContactName nvarchar(30) Contact Title nvarchar(30) M Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) M Fax nvarchar(24) HomePage ntext 000 Region Column Name Condensed Type Allow Nulls Identity 9 RegionID int RegionDescription nchar(50) 000 CustomerCustomerDemo Column Name Condensed Type Allow Nulls Identity 9 CustomerlD nchar(5) CustomerTypeID nchar(10) Employees Column Name Condensed Type Allow Nulls Identity Employeeld int LastName nvarchar(20) FirstName nvarchar(10) Title nvarchar(30) TitleOfCourtesy nvarchar(25) BirthDate datetime HireDate datetime Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) M Country nvarchar(15) HomePhone nvarchar(24) Extension nvarchar(4) Photo image OOOOOOOOOOOOOOOOOOO Orders Column Name Condensed Type Allow Nulls Identity od 9 OrderD int CustomerlD nchar(5) Employeeld int OrderDate datetime RequiredDate datetime ShippedDate datetime ShipVia int Freight money ShipName nvarchar(40) ShipAddress nvarchar(60) Ship City nvarchar(15) Ship Region nvarchar(15) ShipPostalCode nvarchar(10) Ship Country nvarchar(15) D Customer Demographics Column Name Condensed Type Allow Nulls Identity CustomerTypeID nchar(10) CustomerDesc ntext 000 ntext Notes Reports To PhotoPath int nvarchar(255) Shippers Column Name Condensed Type Allow Nulls Identity 8 ShipperID int CompanyName nvarchar(40) Phone nvarchar(24)
