Scribd Logo
Upload

Welcome to Scribd!

  • 8 views

    Day-22 SQL Advanced (Stored Procedures)

    Uploaded by

    kaushik.himanshu.2011

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Day-22 SQL Advanced (Stored Procedures)

    Uploaded by

    kaushik.himanshu.2011
    8 views13 pages

    Original Title

    DAY-22 SQL ADVANCED (STORED PROCEDURES)

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    8 views13 pages

    Day-22 SQL Advanced (Stored Procedures)

    Uploaded by

    kaushik.himanshu.2011

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 13

    DAY-22

    STORED
    PROCEDURES
    IN
    SQL

    SHVETA MAINI
    1

    What is Stored
    Procedure?
    A stored procedure in SQL is a group of
    SQL queries that can be saved and reused
    multiple times and is stored in a database.

    It is very useful as it reduces the need for


    rewriting SQL queries.

    It’s particularly useful for performing


    repetitive tasks, enforcing business rules,
    and optimizing query execution.
    2

    SYNTAX TO CREATE STORED


    PROCEDURE:-
    3

    EXPLANATION OF SYNTAX:-

    CREATE PROCEDURE
    procedure_name:- This starts the
    creation of a new stored procedure
    named procedure_name.

    (parameter1 data_type, parameter2


    data_type,...):- This is where you
    define any input (IN), output (OUT),
    or input-output (INOUT)
    parameters for the procedure.
    4

    EXPLANATION OF SYNTAX:-

    BEGIN ... END:- This block contains


    the SQL statements that make up
    the body of the stored procedure.
    5

    SYNTAX TO EXECUTE STORED


    PROCEDURE:-

    WITHOUT PARAMETERS:-

    WITH PARAMETERS:-
    6

    WHEN TO USE STORED PROCEDURES:-

    1. ) Performance:- Stored procedures


    are already compiled when they’re
    created, so they run faster than
    regular SQL queries that need to be
    processed each time they're run.
    Once a stored procedure is
    created, the database optimizes it,
    making it quicker to execute in
    future runs.
    7

    WHEN TO USE STORED PROCEDURES:-

    2.) Security:- Stored procedures add


    a layer of security because users can
    be granted permission to execute the
    procedure without needing direct
    access to the underlying tables.
    8

    WHEN TO USE STORED PROCEDURES:-

    3.) Maintenance:- Any updates to


    the SQL logic in the procedure only
    need to be made once, in the stored
    procedure itself, instead of in multiple
    places across applications.
    9

    WHEN TO USE STORED PROCEDURES:-

    4.) Complex Calculations:- For


    scenarios requiring complex business
    logic or calculations that are difficult to
    perform in a single query.

    5.) Data Validation:- Use stored


    procedures to enforce rules or validate
    data before performing operations like
    INSERT or UPDATE.
    10

    WHEN TO USE STORED PROCEDURES:-

    6.) Reusability & Efficiency:- If you


    have an SQL query that you write over
    and over again, save it as a stored
    procedure, and then just call it to
    execute it. This enhances efficiency
    and reusability.
    11

    WHEN TO USE STORED PROCEDURES:-

    7.) Report Generation:- You can


    create stored procedures to automate
    report generation by retrieving and
    formatting data as needed.
    I HOPE YOU FIND
    THIS POST
    HELPFUL !
    DO HIT THE LIKE
    BUTTON AND
    SHARE IT
    THANK YOU !!
    SHVETA MAINI

    You might also like