1.查询全部数据
create proc stu1
as
begin
select * from Students
end
go exec stu1
2.根据姓名查询信息
--存储过程内部自带值
create proc stu2
@StuName varchar() ='flt' --字符串长度(50)必须要有
as
begin
select * from Students where StuName=@StuName
end
go exec stu2
--调用存储过程时传参
alter proc stu2
@StuName varchar() --字符串长度()必须有
as
begin
select * from Students where StuName=@StuName
end
go exec stu2 'flt'
3.带output的存储过程
create proc stu3
@StuName varchar(), --字符串长度()必须有
@result varchar() output --输出参数
as
begin
if(select count(*) from Students where StuName=@StuName)>
set @result='存在用户'+@StuName
else
set @result='不存在用户'+@StuName
end
go declare @result varchar()
exec stu3 'flt',@result output
print @result
4.存储过程内部设定局部变量用户名来查询用户信息
--存储过程内部设定局部变量用户名来查询用户信息
create proc stu4
as
declare
@StuName varchar()='flt' --字符串长度()必须有
--@result varchar() output --输出参数
begin
select * from Students where StuName=@StuName
end
go exec stu4
5.根据用户名查询城市
--根据用户名查询城市
create proc stu5
@StuName varchar() --字符串长度()必须有
as
declare
@City varchar()
begin
set @City=(select City from Students where StuName=@StuName)
select @City
end
go exec stu5 'flt'
6.存储过程插入用户信息
--存储过程插入用户信息
create proc stu6
@ID int,
@StuName varchar(),
@Phone varchar(),
@Address varchar(),
@City varchar()
as
begin
insert into Students(ID,StuName,Phone,Address,City)
values (@ID,@StuName,@Phone,@Address,@City)
end
go exec stu6 ,'kxy','','汕头','汕头'
7.删除
create proc stu7
@StuName varchar()
as
begin
delete from Students where StuName=@StuName
return @@rowcount
end
go declare @result int
exec @result=stu7 'kxy'
select @result as 删除条数
8..NET EF框架执行存储过程语句
List<Student> stu= stuen.Students.SqlQuery("exec stu1").ToList();