sql server 存储过程的简单应用

CREATE TABLE [dbo].[账号表] (
    [Id] INT           IDENTITY (1, 1) NOT NULL,
    [账号] NVARCHAR (50) NOT NULL,
    [密码] NVARCHAR (50) NOT NULL,
    [姓名] NVARCHAR (50) NOT NULL,
    [权限] NVARCHAR (50) NOT NULL,
    [头像] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


CREATE TABLE [dbo].[操作记录] (
    [Id]   INT            IDENTITY (1, 1) NOT NULL,
    [时间]   DATETIME       DEFAULT (getdate()) NOT NULL,
    [记录类型] NVARCHAR (50)  NOT NULL,
    [操作人]  NVARCHAR (50)  NOT NULL,
    [名字]   NVARCHAR (50)  NOT NULL,
    [数量]   NVARCHAR (50)  NOT NULL,
    [额外信息] NVARCHAR (100) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);



CREATE PROC [dbo].[修改密码]
@账号s NVARCHAR (50),
@旧密码s NVARCHAR (50),
@新密码s NVARCHAR (50)
AS
begin
IF isnull((select TOP(1) 1 from 账号表 where 账号 = @账号s and 密码=@旧密码s),0)=0
   begin
  INSERT INTO 操作记录(记录类型,操作人,名字,数量,额外信息) VALUES ('修改密码失败',@账号s,@账号s,0,'');
   RETURN 0
   END
ELSE
    update 账号表 set 密码=@新密码s where 账号=@账号s
	  INSERT INTO 操作记录(记录类型,操作人,名字,数量,额外信息) VALUES ('修改密码成功',@账号s,@账号s,0,'密码从 ' + @旧密码s +' 修改为 ' + @新密码s);
    RETURN 1
end


CREATE PROC [dbo].[账号删除]
@账号s NVARCHAR (50),
@密码s NVARCHAR (50)
AS
begin
IF isnull((select TOP(1) 1 from 账号表 where 账号 = @账号s and 密码=@密码s),0)=0
   begin
   	  INSERT INTO 操作记录(记录类型,操作人,名字,数量,额外信息) VALUES ('账号删除失败',@账号s,@密码s,0,'密码不匹配');
  
  RETURN 0
   END
ELSE
 begin
    delete 账号表 where 账号=@账号s and 密码=@密码s
	 INSERT INTO 操作记录(记录类型,操作人,名字,数量,额外信息) VALUES ('账号删除成功',@账号s,@密码s,0,'删除了账号');
    RETURN 1
end
end


CREATE PROC [dbo].[账号注册]
@账号s NVARCHAR (50),
@密码s NVARCHAR (50),
@姓名s NVARCHAR (50)='',
@权限s NVARCHAR (50)='',
@头像s NVARCHAR (50)=''
AS
begin
IF isnull((select TOP(1) 1 from 账号表 where 账号 = @账号s),0)<>0
   begin
   	  INSERT INTO 操作记录(记录类型,操作人,名字,数量,额外信息) VALUES ('账号注册失败',@账号s,@姓名s,0,'重复注册账号');
  
   RETURN 0
   END
ELSE
 begin
    insert into 账号表(账号,密码,姓名,权限,头像) values (@账号s,@密码s,@姓名s,@权限s,@头像s)
	 INSERT INTO 操作记录(记录类型,操作人,名字,数量,额外信息) VALUES ('账号注册成功',@账号s,@姓名s,0,'注册了账号，密码是' + @密码s);
    RETURN 1
end
end


--DECLARE @a int;
--exec @a=修改密码 11,22,33
--select @a
exec 账号删除 '账号','密码'