Stored Procedures

Stored procedures are database objects that group one or more T-SQL statements.

Stored procedures are used to group SQL commands that form a single, logical action. For example, let’s say that we want to add to our website functionality that allows departments to be deleted. However, as we know, we must delete all the department’s employees before we can delete the department itself. To help with such management issues, we could have a stored procedure that copies the employees of that department to another table (called EmployeesBackup),
deletes those employees from the main Employees table, then removes the department from the Department table. As we can imagine, having all this logic saved as a stored procedure can make working with databases much easier.

The basic form of a stored procedure is as follows:

CREATE PROCEDURE ProcedureName
(
  @Parameter1 DataType,
  @Parameter2 DataType,
  ⋮ 
)
AS
  -- an optional comment
  ⋮ SQL Commands

The leading “--” marks a comment. The parameter names, as well as the names of
variables we can declare inside stored procedures, start with @. As we might expect,
their data types are the same data types supported by SQL Server.

The stored procedure shown below creates a new department whose name is specified through the first parameter. It then creates a new employee whose name is specified as the second parameter, assigns the new employee to the new department, and finally deletes both the new employee and the new department.

CREATE PROCEDURE DoThings
(
  @NewDepartmentName VARCHAR(50),
  @NewEmployeeName VARCHAR(50),
  @NewEmployeeUsername VARCHAR(50)
)
AS
-- Create a new department
INSERT INTO Departments (Department)
VALUES (@NewDepartmentName)
-- Obtain the ID of the created department
DECLARE @NewDepartmentID INT
SET @NewDepartmentID = scope_identity()
-- Create a new employee
INSERT INTO Employees (DepartmentID, Name, Username)
VALUES (@NewDepartmentID, @NewEmployeeName, @NewEmployeeUsername)
-- Obtain the ID of the created employee
DECLARE @NewEmployeeID INT
SET @NewEmployeeID = scope_identity()
-- List the departments together with their employees
SELECT Departments.Department, Employees.Name
FROM Departments
INNER JOIN Employees ON Departments.DepartmentID =
    Employees.DepartmentID
-- Delete the new employee
DELETE FROM Employees
WHERE EmployeeID = @NewEmployeeID
-- Delete the new department
DELETE FROM Departments
WHERE DepartmentID = @NewDepartmentID

Execute this code to have the DoThingsstored procedure saved to our database. We can now execute our new stored procedure by supplying the required parameters as follows:

EXECUTE DoThings 'Research', 'Cristian Darie', 'cristian'

If we execute the procedure multiple times, We’ll achieve the same results, since
any data that’s created as part of the stored procedure is deleted at the end of the
stored procedure:

(1 row(s) affected)
(1 row(s) affected)
Department                       Name
-------------------------------- --------------------------------
Executive                        Zak Ruvalcaba
Marketing                        Jessica Ruvalcaba
Engineering                      Ted Lindsey
Engineering                      Shane Weebe
Marketing                        David Levinson
Accounting                       Geoff Kim
Research                         Cristian Darie
(7 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)