SQL Stored Procedures for SQL Server

A stored procedure is a prepared SQL code that you can save and reuse. So if you have a SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

Creating a Stored Procedure

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

Executing a Stored Procedure

EXEC procedure_name;

EXEC GetAllEmployees;

Stored Procedure with Parameters

CREATE PROCEDURE procedure_name
    @parameter_name datatype,
    @parameter_name datatype
AS
BEGIN
    -- SQL statements
END;

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Executing a Stored Procedure with Parameters

EXEC procedure_name @parameter1 = value1, @parameter2 = value2;

EXEC GetEmployeeByID @EmployeeID = 1;

Modifying a Stored Procedure

To modify an existing stored procedure, use the ALTER PROCEDURE statement:

ALTER PROCEDURE procedure_name
AS
BEGIN
    -- New SQL statements
END;

ALTER PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END;

Deleting a Stored Procedure

DROP PROCEDURE procedure_name;

DROP PROCEDURE GetAllEmployees;

Sample Tables

Consider the following Employees table:

Employees:
| EmployeeID | FirstName | LastName | Position       |
|------------|-----------|----------|----------------|
| 1          | John      | Doe      | Manager        |
| 2          | Jane      | Smith    | Developer      |
| 3          | Michael   | Johnson  | Sales Associate |

Important Notes

• Stored procedures help in reducing the traffic between application and database server.
• They also provide an extra layer of security by restricting direct access to tables.
• Parameters in stored procedures can be used to pass and receive data.
• Use ALTER PROCEDURE to modify an existing procedure and DROP PROCEDURE to delete it.