Chapter 8: Speaking SQL
The command above would execute successfully because there aren’t any employees linked to the new department.
Deleting Records
Like the UPDATE command, the WHERE clause is best used together with DELETE; otherwise, you can end up deleting all the records in the table inadvertently!
Stored Procedures
Stored procedures are database objects that group one or more T-SQL statements. Much like VB or C# functions, stored procedures can take parameters and return values.
Stored procedures are used to group SQL commands that form a single, logical action. For example, let’s say that you want to add to your web site functionality that allows departments to be deleted. Now, as you know, you must delete all of the department’s employees before you can delete the department itself.
To help with such management issues, you 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 you can imagine, having all this logic saved as a stored procedure can make working with databases much easier.
We’ll see a more realistic example of a stored procedure in the next chapter, when we start to add more features to the Dorknozzle project, but until then, let’s learn how to create a stored procedure in SQL Server, and how to execute it.
The basic form of a stored procedure is as follows:
CREATE PROCEDURE ProcedureName
(
@Parameter1 DataType,
@Parameter2 DataType,
)
AS
-- SQL Commands here
326
Stored Procedures
The leading “--” marks a comment. The parameter names, as well the names of variables we can declare inside stored procedures, start with @. As you 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. Now, such a stored procedure wouldn’t make much sense in reality, but this example allows you to learn a few interesting details that you’ll be using frequently as you develop applications, and it uses much of the theory you’ve learned in this chapter.
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
327
Chapter 8: Speaking SQL
Execute this code to have the DoThings stored procedure saved to your Dorknozzle database. You can now execute your new stored procedure by supplying the required parameters as follows:
EXECUTE DoThings 'Research', 'Cristian Darie', 'cristian'
If you execute the procedure multiple times, you’ll get 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) |
|
So, what does the stored procedure do? Let’s take a look at the code piece by piece.
The beginning of the stored procedure code specifies its name and its parameters:
CREATE PROCEDURE DoThings
(
@NewDepartmentName VARCHAR(50), @NewEmployeeName VARCHAR(50), @NewEmployeeUsername VARCHAR(50)
)
AS
The parameters include a department name, an employee name, and an employee username.
328
Stored Procedures
CREATE PROCEDURE and ALTER PROCEDURE
To modify an existing stored procedure, you’ll need to use ALTER PROCEDURE instead of CREATE PROCEDURE. Feel free to play with your existing procedure, to get a feel for how this works.
The code of the stored procedure starts by creating a new department with the name specified by the @NewDepartmentName parameter:
-- Create a new department
INSERT INTO Departments (Department) VALUES (@NewDepartmentName)
Immediately after it creates the department, the stored procedure stores the value generated for the IDENTITY primary key column (DepartmentID). This value is returned by the scope_identity function, which returns the most recently generated identity value. Keep in mind that it’s good practice to store this identity value right after the INSERT query that generated it; if we don’t store this value immediately, a second INSERT query may generate another identity value, and that second identity value would then be returned by scope_identity. The value is saved into a new variable named @NewDepartmentID.
-- Obtain the ID of the created department DECLARE @NewDepartmentID INT
SET @NewDepartmentID = scope_identity()
Here, you can also see how we use the DECLARE statement to declare a new variable in an SQL stored procedure.
The stored procedure continues by creating a new employee using the name and username it received as parameters; it assigns this employee to the department that was created earlier:
-- Create a new employee
INSERT INTO Employees (DepartmentID, Name, Username)
VALUES (@NewDepartmentID, @NewEmployeeName, @NewEmployeeUsername)
Again, right after creating the new employee, we store its ID into a variable named @NewEmployeeID. Earlier, we needed to store the generated DepartmentID so that we could assign the new employee to it; this time, we’re storing the new employee ID so we can delete the employee later.
-- Obtain the ID of the created employee DECLARE @NewEmployeeID INT
SET @NewEmployeeID = scope_identity()
329