Stored Procedure is a block of code given a name which is stored in the database. This block of code can include:

• SQL Queries

• DML, DCL, DDL, and TCL commands.

• Collection Types

• Loop & IF ELSE statements

• Variables

• Exception Handling

• Cursors Procedures not only queries data from the table but can be used to build complex logic, data validation, data clean ups and much more. The reason why Procedures are so powerful is because we can do alot more in a procedure than we can use an SQL commands. It gives more power to SQL language i.e it used to do things that is generally not possible in SQL queries.

Syntax to create a Procedure

First we need to create a database. To create the database we will be using:

create database productSales;

We need to tell SQL to use the database:

use database productSales;

Next, we need to create a table (product and sales):

create table product(
    product_code VARCHAR(25),
    product_name VARCHAR(30),
    price float ,
    quantity_remaining int,
    quantity_sold int
)

The we need to insert into the created table:

insert into product( product_code, product_name, price, quantity_remaining, quantity_sold)
values('prod001', 'Product1', 10000, 40, 0),
('prod002', 'Product2', 20000, 23, 0),
('prod003', 'Product3', 15000, 10, 0),
('prod004', 'Product4', 13000, 35, 0),
('prod005', 'Product5', 15000, 15, 0),
('prod006', 'Product6', 17000, 17, 0),
('prod007', 'Product7', 12000, 43, 0),
('prod008', 'Product8', 18000, 12, 0),
('prod009', 'Product9', 20000, 18, 0),
('prod0010', 'Product10', 34000, 16, 0)

We then create the sales table:

create table sales(
    product_code VARCHAR(25),
    price float ,
    quantity_ordered int,
)

To check the just created tables:

select * from product;


select * from sales

To create a procedure in MSSQL , you use the command CREATE PROCEDURE then pass in the name of the procedure. This is followed by AS BEGIN which takes the command you are trying to execute. In the example below, i declared two variables(@v_product_code and @v_price) . The SELECT statement matches the declared variable to the column name in the table. The INSERT statement inserts the matched records in the sales table and the UPDATE statement, updates the value in the product table after a successful insert.

CREATE PROCEDURE saleRecords
AS
BEGIN
    DECLARE @v_product_code VARCHAR(25),
            @v_price FLOAT;

    SELECT @v_product_code = product_code , @v_price = price from product where product_name= 'Product1';

    INSERT INTO sales (product_code , price, quantity_ordered) values(@v_product_code, @v_price, 1);

    UPDATE product
    set quantity_remaining = (quantity_remaining - 1),
    quantity_sold  = (quantity_sold + 1)
    where product_code = @v_product_code;

    print('Transaction Complete')

END;

To use the just created procedure, we need to call it, we can do this in the existing workspace or open a new queryTo execute the

select * from product;
select * from sales;

exec pr_buy_products()

Stored Procedure with Parameters

To start with, we need to CREATE PROCEDURE followed by the name then the parameters we want to collect with their data types, in this case I would like to collect product name and quantity to be bought. Then we need to declare the variables we want to use in the procedure. I need to check if the count of product demanded is less than the count of product available( i.e I can't sell 5 products when I have just 2 available). Just like the previous one we wrote, the SELECT statement matches the declare values to the columns in the table , product. INSERT statement inserts the records of the user into the sales table and multiplies the quantity bought by the unit price(i.e if a product costs 10000 and I purchase 2 of that, I get to pay 20000). The UPDATE statement , updates the records in the product table by deducting the quantity sold from the inventory and adding it the count of product sold(quantity sold)

CREATE PROCEDURE pr_buy_product(@p_name varchar(25), @quantity int)
AS
    DECLARE 
        @v_product_code varchar(25),
        @v_price FLOAT,
        @v_cnt INT ;

    BEGIN
        SELECT @v_cnt = COUNT(1)
        FROM product
        WHERE product_name = @p_name
        AND quantity_remaining >= @quantity;


    IF @v_cnt > 0
    BEGIN 
        SELECT @v_product_code = product_code , @v_price = price from product where product_name= @p_name;

        INSERT INTO sales (product_code , price, quantity_ordered) values(@v_product_code, (@v_price * @quantity), @quantity);

        UPDATE product
        set quantity_remaining = (quantity_remaining - @quantity),
        quantity_sold  = (quantity_sold + @quantity)
        where product_code = @v_product_code;

    END
    ELSE
        print('Invalid Transaction');

END;

To the interesting part which is testing our code to see if it works:

SELECT * FROM product;


SELECT * FROM sales;

EXEC pr_buy_product 'Product6', 1;

To begin we can check the individual tables(product and sales) before executing the procedure. The EXEC command followed by the procedure name and then the parameters we stated in the procedure(product_name and quantity).

Have fun!!!