Stored Procedures

Stored procedures are a set of Transact-SQL statements that are compiled and stored in a database. Stored procedures can accept parameters, return values, and perform a wide range of database operations. They are used to encapsulate complex business logic, improve performance, and enhance security.

Benefits of Stored Procedures

Performance: Stored procedures are compiled and optimized the first time they are executed. Subsequent executions benefit from the cached execution plan, leading to faster performance compared to ad-hoc queries.
Reusability: They allow you to write complex logic once and call it from multiple applications or user interfaces, promoting code reuse and reducing redundancy.
Modularity: Stored procedures break down complex database operations into smaller, manageable units, making development and maintenance easier.
Security: You can grant users permission to execute stored procedures without granting them direct access to the underlying tables, enhancing data security and preventing unauthorized modifications.
Network Traffic Reduction: Instead of sending multiple SQL statements over the network, only the call to the stored procedure is sent, reducing network traffic.

Creating a Stored Procedure

The basic syntax for creating a stored procedure is as follows:


CREATE PROCEDURE procedure_name
    [ @parameter1 datatype = default_value,
      @parameter2 datatype = default_value ]
AS
BEGIN
    -- Transact-SQL statements
    SELECT 'Hello, ' + @parameter1;
END;
GO

Executing a Stored Procedure

Stored procedures are executed using the EXECUTE or EXEC command:


EXECUTE procedure_name @parameter1 = 'World';
GO

-- or

EXEC procedure_name 'World';
GO

Modifying and Dropping Stored Procedures

To modify an existing stored procedure, use ALTER PROCEDURE. To remove a stored procedure, use DROP PROCEDURE.


ALTER PROCEDURE procedure_name
    @parameter1 datatype = new_default_value
AS
BEGIN
    -- Updated Transact-SQL statements
    SELECT 'Greetings, ' + @parameter1;
END;
GO

DROP PROCEDURE procedure_name;
GO

Microsoft Learn