存储过程

use bawei
go

--select * from student

 

---判断存储过程是否存在
if exists(select * from sysobjects where name='up_searchStudent')
---如果存在先删除
drop proc up_searchStudent
go

---创建存储过程
create proc up_searchStudent
--定义两个参数，赋默认值
@name varchar(20) =null,
@Gid  varchar(2) =null,
@count int output
as

print len(@name)
print len(@Gid)

---判断参数为空，执行不同的查询条件
if((@name is not null and @name!='') and (@Gid is not null and @Gid!=''))
begin
 select * from student where SName like '%'+@name+'%' and GradeGId = @Gid
 select @count= count(1) from student where SName like '%'+@name+'%' and GradeGId = @Gid
end
else if(@Gid is not null and @gid !='')
begin
 select * from student where  GradeGId = @Gid
 select @count= count(1) from student where  GradeGId = @Gid
end
else if(@name is not null and @name!='')
begin
 select * from student where SName like '%'+@name+'%'
 select @count= count(1) from student where SName like '%'+@name+'%'
 end
else
begin
 select * from student
 select @count= count(1) from student
end

go
--调用存储过程
declare @count int
exec up_searchStudent  '','', @count output
select @count

---创建添加学生的存储过程

use bawei
go
if exists(select * from sysobjects where name='up_addStudent')
drop proc up_addStudent
go

create proc up_addStudent
@sname varchar(40),
@age int,
@sex bit,
@gid int,
@hobby varchar(50),
@headImg varchar(100),
@result int output
as

 

insert into student values(@sname,@age,@sex,@gid,@hobby,@headImg)

--判断如果错误号大于0，则返回0
if(@@error>0)
 set @result=0
else
 set @result=1
go

---调用存储过程
declare @res int

exec up_addStudent '张三李四张三李四张三李四张三李四张三李四张三李四',12,'true',1,'打豆豆','头像',@res output

print @res

--存储过程登录

--是否有这个存储过程
if exists(select * from sysobjects where name='P_User')
drop proc P_User
go

create proc P_User
@UName nvarchar(20),
@Pwd nvarchar(20),
@Out int output
as
begin
select @Out=count(*) from UserInfo where UName=@UName and Pwd=@Pwd
end
go

declare @res int

exec P_User '11','22',@res output

if(@res>0)
print '登录成功'
else
print '登录失败'