要开发用户管理系统,我们首先要了解需求,现在就举一个简单需求,用户表,假设有两种角色用一个字段departID来判断,管理员和员工,
我们要先建一个用户表custom和一个部门表department:
CREATE TABLE [dbo].[custom]( [id] [int] IDENTITY(1,1) NOT NULL, [cname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [departID] [int] NOT NULL, [age] [int] NOT NULL, [ename] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_custom] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; CREATE TABLE [dbo].[department]( [id] [int] IDENTITY(1,1) NOT NULL, [departname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [description] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]建完数据库表后,开始写存储过程,插入一条数据:
CREATE PROCEDURE [dbo].[spInsertCustom] @cname nvarchar(50), @ename nvarchar(50), @age int, @departID int, @password nvarchar(50) AS BEGIN insert into custom(cname,departID,age,ename,password) values (@cname,@departID,@age,@ename,@password) END RETURN @@Identity现建两个更新一条数据的存储过程:
CREATE PROCEDURE [dbo].[spupdatecustom]
@id int,
@cname nvarchar(50),
@departID int,
@age int,
@ename nvarchar(50),
@password nvarchar(50)
AS
BEGIN
update
custom
set
cname = @cname,
departID = @departID,
age = @age,
ename = @ename,
password = @password
where id = @id
END
COMMIT TRAN再新建两个取出所有用户的存储过程:CREATE PROCEDURE [dbo].[spGetcustom] AS BEGIN select * from custom order by id desc END再新建一个根据ID取出一条数据的存储过程:
CREATE PROCEDURE [dbo].[spGetcustomer] @id int AS BEGIN select * from custom where id = @id END
评论