A stored procedure is a precompiled SQL statement or set of statements that can be executed on demand. It is a subroutine that can be called by other SQL statements and applications. Stored procedures can be used to encapsulate business logic, enforce security policies, and improve performance by reducing network traffic.Creating a stored procedure in SQL involves writing a block of code and saving it in the database for later use. The basic syntax for CREATE PROCEDURE is as follows:

CREATE PROCEDURE procedure_name
    [parameter declarations]
AS
BEGIN
    statement(s)
END;

Here’s an example of creating a stored procedure that inserts a new record into a table:

CREATE PROCEDURE insert_employee
    @name VARCHAR(50),
    @age INT,
    @salary DECIMAL(10,2)
AS
BEGIN
    INSERT INTO employees (name, age, salary)
    VALUES (@name, @age, @salary)
END;

This stored procedure is named “insert_employee” and takes three parameters: name, age, and salary. It uses these parameters to insert a new record into the “employees” table.

To execute the stored procedure, we can use the following SQL statement:

EXEC insert_employee 'John Smith', 35, 50000.00;

This statement will call the “insert_employee” stored procedure and pass in the values ‘John Smith’, 35, and 50000.00 for the parameters.

We can also use the “CREATE OR ALTER” statement to create a stored procedure if it doesn’t exist, or alter it if it does. For example:

CREATE OR ALTER PROCEDURE insert_employee
    @name VARCHAR(50),
    @age INT,
    @salary DECIMAL(10,2)
AS
BEGIN
    INSERT INTO employees (name, age, salary)
    VALUES (@name, @age, @salary)
END;

This statement will create the “insert_employee” stored procedure if it doesn’t exist, or alter it if it does.

Stored procedures can also return values using the “RETURN” statement, output parameters, or result sets. Here’s an example of a stored procedure that returns the average salary of all employees:

CREATE PROCEDURE get_average_salary
AS
BEGIN
    SELECT AVG(salary) AS average_salary
    FROM employees
END;

We can execute this stored procedure and retrieve the result set using the following SQL statement:

EXEC get_average_salary;

Stored procedures can be a powerful tool in SQL for encapsulating business logic and improving performance. By creating reusable code blocks, you can simplify complex operations and make them more efficient.

Learn SQL Now: Learn From Scratch to Advanced