Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Question: Please follow the steps below. The goal is to create a stored procedure that uses TRY/CATCH and error handling functions. The stored procedure, MyOrdersInsert,

Question: Please follow the steps below.

The goal is to create a stored procedure that uses TRY/CATCH and error handling functions. The stored procedure, MyOrdersInsert, 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 orderdate and must be the DATETIME data type. The second input parameter is for CustomerName and must be of the VARCHAR(100) data type. The third parameter is an output parameter and is an integer datatype. It 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 error functions (except for the ErrorDateTime, which must use the get date function).

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

image text in transcribed

Messages Erorogld ErmorDate Time 2017-04-11 10:08:18.310 547 2017-04-11 10:08:18.310 515 MyOndersinsert The INSERT statement conficted with the CHECK c MyOrderslnsert Cannot insert the value NULL into column Customer MyOrdersld OrderDate 3 CustomerName 1 201704-11 10:08:18.310 E.F. Codd Results --(Message #1 ?xec with FutureDate return code 547 and new OrderId of 0 #2 Exec with NULLName return code = 515 and new OrderId f 0 #3 Exec with good data return code-0 and new OrderId of 3 Messages Erorogld ErmorDate Time 2017-04-11 10:08:18.310 547 2017-04-11 10:08:18.310 515 MyOndersinsert The INSERT statement conficted with the CHECK c MyOrderslnsert Cannot insert the value NULL into column Customer MyOrdersld OrderDate 3 CustomerName 1 201704-11 10:08:18.310 E.F. Codd Results --(Message #1 ?xec with FutureDate return code 547 and new OrderId of 0 #2 Exec with NULLName return code = 515 and new OrderId f 0 #3 Exec with good data return code-0 and new OrderId of 3

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Larry Ellison Database Genius Of Oracle

Authors: Craig Peters

1st Edition

0766019748, 978-0766019744

More Books

Students also viewed these Databases questions

Question

6. Describe why communication is vital to everyone

Answered: 1 week ago