Prerequisites for Creating Procedure

Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that.
Example Scenario:

Suppose there are 3 tables PRODUCTS, REGION and SALES.

We need to find out sales value for different region. We also need to calculate NET_AMOUNT based on the DISCOUNT. DISCOUNT value will be passed as input parameter. 

We will create a procedure to achieve this. 

Create Tables:

In the chapter 1, we have created 3 tables PRODUCTS, REGION and SALES. If you have created them earlier, then create it. 

Create Table Type:

We need to create a table type, which will be used for output parameter of the procedure. 

Execute the below SQL statement. 

--REPLACE SCHEMA_NAME WITH YOUR SCHEMA

CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (

            SALES_AMOUNT DECIMAL,

            NET_AMOUNT DECIMAL,

            PRODUCT_NAME NVARCHAR(20),

            REGION_NAME NVARCHAR(20),

            SUB_REGION_NAME NVARCHAR(20)

);

Go to chapter 4.4 to know more about table type.

Click here to go to Next Chapter. 5.3 Create Procedure Using the SQL editor