Background

SERVER chap 12, p. 435-472

Previous exercises (basis for this exercise) PetersPanSQLexercise1.htm

Assignment 1 Simple Stored Procedure

For the OrderLine table, make a stored procedure 'SP_GetAllOrders' to 'just' select alle rows.

Make a call of this procedure to see it works.

Assignment 2 Procedure with parameter

Make a similar procedure 'SP_GetOrderFromCutomer' which take in a parameter the customer number
and only select the relevant orderline for this customer.

Make a call of this procedure to see it works - try with an existing cutomer and one which do not exists.

Modify the procedure to accept a default parameter (for mysql see https://forums.mysql.com/read.php?98,27783,27783)
If there is no parameter - then return all orderlines otherwise only orderlines for the specified customer.

Make a call with and without an CustomerNo

Assignment 3 Procedure with inout and output parameters

Make a procedure 'SP_GetReceipt' which find the orderlines for a customer and calculate the total price.
The procedure must return the total price in a parameter

Make a call of this procedure

Assignment 4 Procedure Change An menuItem for a Customer

Make a stored procedure 'SP_ChangeOrder' that change the order for a Customer from one MeuItem to another.
I.e. the procedure should have three parameters CustomerNo, FromMenuItemNo and ToMenuItemNo.

Make a call of this procedure.

Assignment 5 Procedure Get Next 10 rows

Make a stored procedure 'SP_GetNextRows' that from a parameter find the next 10 rows.

(for mssql: https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server)
(for mysql: https://stackoverflow.com/questions/5767479/mysql-limit-range)

Make a call of this procedure.

Something with transactions

Assignment 6 Transactions

Make a similar procedure to the one assignment 4, But this time add 2 elements

1. Begin transaction and finish with rollback or comit

(for mysql: https://dev.mysql.com/doc/refman/5.7/en/commit.html)
(for mssql: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql)

2. Catching errors

(for mysql: https://stackoverflow.com/questions/35867207/exception-using-try-catch-in-mysql-stored-procedure)
(for mssql: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql)

The idea is to exchange the MenuItem, both both sql-statement must be fullfilled before the exchange have been succeded.
To catch if the sql statement are succeded use try-catch.

Make a call of this procedure.