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;Example:
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * FROM Employees;
END;This stored procedure retrieves all records from the Employees table.
Executing a Stored Procedure
EXEC procedure_name;Example:
EXEC GetAllEmployees;This executes the GetAllEmployees stored procedure.
Stored Procedure with Parameters
CREATE PROCEDURE procedure_name
@parameter_name datatype,
@parameter_name datatype
AS
BEGIN
-- SQL statements
END;Example:
CREATE PROCEDURE GetEmployeeByID
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;This stored procedure retrieves an employee by their ID.
Executing a Stored Procedure with Parameters
EXEC procedure_name @parameter1 = value1, @parameter2 = value2;Example:
EXEC GetEmployeeByID @EmployeeID = 1;This executes the GetEmployeeByID stored procedure with the EmployeeID parameter set to 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;Example:
ALTER PROCEDURE GetAllEmployees
AS
BEGIN
SELECT EmployeeID, FirstName, LastName FROM Employees;
END;This modifies the GetAllEmployees stored procedure to select specific columns.
Deleting a Stored Procedure
DROP PROCEDURE procedure_name;Example:
DROP PROCEDURE GetAllEmployees;This deletes the GetAllEmployees stored procedure.
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 PROCEDUREto modify an existing procedure andDROP PROCEDUREto delete it.