To keep things simple create a database in SQL Server 2008
called OrderSystem and add a single table called UserAccounts to the database. The
fields for this table are shown in the following diagram.
The Id field should be marked as an Identity field and also the
Primary Key. All access to the table will be done with stored procedures. Add
the following stored procedures to the OrderSystem Database.
1.
The UserAccounts_SelectAll procedure returns a resultset containing all
the records in the UserAccounts table.
CREATE PROCEDURE [dbo].[UserAccounts_SelectAll]
AS
SET NOCOUNT ON
SELECT Id, FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate
FROM UserAccounts
RETURN
2.
The UserAccounts_SelectById procedure returns a single record from the
UserAccounts table given a specific Id.
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
3.
The UserAccounts_Insert procedure adds a record to the UserAccounts
table.
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 CAST(SCOPE_IDENTITY() AS INT) AS Id
4.
The UserAccounts_Update 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
5.
The UserAccounts_Delete procedure deletes a record in the UserAccounts
table given a specific Id.
CREATE PROCEDURE [dbo].[UserAccounts_Delete]
(
@Id int
)
AS
SET NOCOUNT ON
DELETE
FROM UserAccounts
WHERE Id = @Id
RETURN
