Prev Next

Stored Procedures

A STORED PROCEDURE is a set of SQL statements that you can save and reuse. Stored procedures are used to perform operations like querying or updating the database and can accept parameters, making them powerful tools for repetitive tasks.

Creating a Stored Procedure

Use the CREATE PROCEDURE statement to create a stored procedure. The basic syntax is:

CREATE PROCEDURE ProcedureName
AS
BEGIN
    -- SQL statements
END;

Example: Simple Stored Procedure

CREATE PROCEDURE GetAllFreedomFighters
AS
BEGIN
    SELECT * FROM FreedomFighters;
END;

Explanation: This stored procedure, GetAllFreedomFighters, retrieves all records from the FreedomFighters table.

Executing a Stored Procedure

Use the EXEC statement to execute a stored procedure:

EXEC GetAllFreedomFighters;

Stored Procedure with Parameters

Stored procedures can accept input parameters to filter or modify the results.

Example: Stored Procedure with Parameters

CREATE PROCEDURE GetFreedomFightersByCity @City VARCHAR(50)
AS
BEGIN
    SELECT * FROM FreedomFighters WHERE City = @City;
END;

Executing with Parameters

EXEC GetFreedomFightersByCity @City = 'Chennai';

Explanation: This command executes the stored procedure GetFreedomFightersByCity and retrieves records for freedom fighters from Chennai.

Updating a Stored Procedure

To modify a stored procedure, use the ALTER PROCEDURE statement:

ALTER PROCEDURE GetAllFreedomFighters
AS
BEGIN
    SELECT Name, City FROM FreedomFighters;
END;

Dropping a Stored Procedure

To delete a stored procedure, use the DROP PROCEDURE statement:

DROP PROCEDURE GetAllFreedomFighters;

Do's and Don'ts

Do's

  • Use stored procedures to improve performance by reducing the amount of information sent to the database server.
  • Use parameters to make stored procedures flexible and reusable.
  • Document your stored procedures for easy maintenance.

Don'ts

  • Don't overuse stored procedures for simple queries that can be written directly.
  • Don't forget to handle errors and exceptions in your stored procedures.
  • Don't expose sensitive information through stored procedures without proper security measures.
Prev Next