The first step is to create the stored procedures in the
database. The first stored procedure is used to select all the records from
the UserAccounts table.
CREATE PROCEDURE dbo.UserAccounts_SelectAll
AS
SET NOCOUNT ON
SELECT Id, FirstName, LastName, AuditFields_InsertDate,
AuditFields_UpdateDate
FROM UserAccounts
RETURN
The next procedure will select a single record from the
UserAccounts table using the Id in the where clause.
CREATE PROCEDURE dbo.UserAccounts_SelectById
(
@Id int
)
AS
SET NOCOUNT ON
SELECT Id, FirstName, LastName, AuditFields_InsertDate,
AuditFields_UpdateDate
FROM UserAccounts
WHERE Id = @Id
RETURN
The third procedure inserts a record into the UserAccounts
table. This takes all the fields as parameters and returns the Id of the
inserted record.
CREATE PROCEDURE dbo.UserAccounts_Insert
(
@FirstName nvarchar(50),
@LastName nvarchar(50),
@AuditFields_InsertDate datetime,
@AuditFields_UpdateDate datetime
)
AS
INSERT INTO UserAccounts (FirstName, LastName, AuditFields_InsertDate,
AuditFields_UpdateDate)
VALUES (@FirstName, @LastName, @AuditFields_InsertDate,
@AuditFields_UpdateDate)
SELECT SCOPE_IDENTITY() AS Id
The fourth procedure updates a record in the UserAccounts
table.
CREATE PROCEDURE dbo.UserAccounts_Update
(
@Id int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@AuditFields_UpdateDate datetime
)
AS
SET NOCOUNT ON
UPDATE UserAccounts
SET FirstName = @FirstName,
LastName = @LastName,
AuditFields_UpdateDate = @AuditFields_UpdateDate
WHERE Id = @Id
RETURN
The fifth procedure deletes a record from the UserAccounts
table.
CREATE PROCEDURE dbo.UserAccounts_Delete
(
@Id int
)
AS
SET NOCOUNT ON
DELETE
FROM UserAccounts
WHERE Id = @Id
RETURN
You should create all of these stored procedures in the
OrderSystem database before moving to step 2.