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
