Stored Procedure For Insert,Delete,Update,Select Commands

Before you can start coding the data access logic, you need to make sure you have the set of stored
procedures you need in order to retrieve, insert, and update information. The following code shows
the five stored procedures that are needed:


CREATE PROCEDURE InsertEmployee
@EmployeeID int OUTPUT
@FirstName varchar(10),
@LastName varchar(20),
@TitleOfCourtesy varchar(25),
AS
INSERT INTO Employees
(TitleOfCourtesy, LastName, FirstName, HireDate)
VALUES (@TitleOfCourtesy, @LastName, @FirstName, GETDATE());
SET @EmployeeID = @@IDENTITY
GO


CREATE PROCEDURE DeleteEmployee
@EmployeeID int
AS
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
GO


CREATE PROCEDURE UpdateEmployee
@EmployeeID int,
@TitleOfCourtesy varchar(25),
@LastName varchar(20),
@FirstName varchar(10)
AS
UPDATE Employees
SET TitleOfCourtesy = @TitleOfCourtesy,
LastName = @LastName,
FirstName = @FirstName
WHERE EmployeeID = @EmployeeID
GO


CREATE PROCEDURE GetAllEmployees
AS
SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees
GO


CREATE PROCEDURE CountEmployees
AS
SELECT COUNT(EmployeeID) FROM Employees
GO


CREATE PROCEDURE GetEmployee
@EmployeeID int
AS
SELECT FirstName, LastName, TitleOfCourtesy FROM Employees
WHERE EmployeeID = @EmployeeID
GO