使用存储过程进行基本的增删改查

 

 

 

create proc sp_getAdmin

@uId int output

as

 select * from userInfo where @uId =Id

go

exec sp_getAdmin @uId=1

 

 

 

 

create proc sp_Update

@uId int ,

@userName varchar(50) 

as

 update userInfo set UserName=@userName where @uId =Id

go

exec sp_Update @uId=1,@userName='administrator'

 

 

create proc sp_delete

@uId int

as

 delete from userInfo where Id=@uId

go

exec sp_delete 2

 

create proc sp_Insert

@userName varchar(50),

@passWord varchar(50),

@userStateId int 

as

 insert into UserInfo(UserName,PassWord,UserStateId)

 values(@userName,@passWord,@userStateId)

go

exec sp_Insert 'guest','123456',2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATE procedure [CRIdeoClass]

@UserID int,--学生ID

@ClassID int,--课堂ID

@Money int,--授课点数

@Discription text--来插入消费记录的详细信息

 

as

declare @count int,@return int,@people int

begin

select @count=count(*) from ideoClass where Money<(select CardCount from u_info_t where ID=@UserID) and ID=@ClassID

if(@count<1)

--点数不够，请充值后在购买

select @return=-1

else

begin

select @count=Count(*) from ConSump where ClassID=@ClassID and UID=@UserID and Type=1

if(@count>0)

--您已经申请过此课程

select @return=-2

else

begin

select @people=people from ideoClass where ID=@ClassID

if(@people<1)

--此课堂人数为零或没有此课堂

select @return=-3

else

begin

select @count=count(*) from ConSump where ClassID=@ClassID 

if(@count>@people)

--此课堂已经申请人满

select @return=-4

else

begin

insert Consump (UID,Amount,AddDate,Description,Type,ClassID) values (@UserID,@Money,getdate(),@Discription,1,@ClassID) 

if(@@error>0)

--插入消费记录失败，请联系管理员

select @return=-5

else 

begin

update u_info_t set CardCount=(CardCount-(select Money from ideoClass where ID=@ClassID)) where ID=@UserID --更新学生点数

if(@@error>0)

--更新学生点数失败，请联系管理员

select @return=-6

else

--恭喜你，您已经申请成功

select @return=1

end

end

end

end

end

end

return @return

GO