SQL – Stored Procedure

Spread the love

Stored Procedure – multiple business logical and tasks can be defined in a single Stored Procedure

Benefits of Stored Procedure

  • Multiple business logic can be defined in single stored procedure, and all the specified users can have the access to use it.
  • A process of business logic can be commented  with the statement/query, which saves lots of time to create technical document.
  • It helps to avoid the extra load on database/server, also, saves the time that different users writing the same business logic and duplicate query writing on the same database.
  • if business logic has to be changed then needs to change only once for all.

 

Stored procedure can be written with the parameter, which allow a user to input the value at the time of execution.

 

ITEM_TYPE TABLE

S_NOPRODUCTTYPE
1NIKESHOES
2REEBOKTSHIRT
3FILASHOES
4PUMARUNNING SHOES
5ADIDASSHOES

BRAND TABLE

S_NOPRODUCTMONTHYEARQTYSALES
1NIKEDEC2016107599
2REEBOKJAN2014181200
3FILAFEB201786000
4PUMAJAN2015165000
5NULLAPR201252500

 

Manipulation can be done multiple time within the  procedure, just need  to define within ( begin  …….    end)

CREATE PROCEDURE DATAVIEW(@YEAR_FLAGE AS INT )

RETURNS TABLE
           AS
                     BEGIN
                     DECLARE @DM INT
                     SET @DM = @YEAR_FLAGE
                    END

BEGIN
             SELECT A.S_NO , A.PRODUCT , B.TYPE from BRAND AS A
             LEFT JOIN
             ITEM_TYPES B
            ON A.PRODUCT = B.PRODUCT
            WHERE A.YEAR = @DM
END

 

Stored procedure is located under programmability

STORED PROCEDURE