存储过程实现基本的增删查改

三个表结构：

不废话直接上代码

use Text      --数据库名

if exists
(select name from sysobjects where name ='up_getallstudents' and type ='p')
drop procedure up_getallstudents
--编写存储过程up_getallstudents,用于获取学生表students的所有记录 
GO
create procedure up_getallstudents 
as
select * from T_stu_cou
--调用
exec up_getallstudents



if exists
(select name from sysobjects where name ='up_insertstudent' and type ='p')
drop procedure up_insertstudent
GO
create procedure up_insertstudent
@sid int,
@sname varchar(30),
@sphone bigint
as
begin
insert into T_Stuinfo(stu_id,stu_name,stu_phone)
values
(@sid,@sname,@sphone)
end


--调用
exec up_insertstudent 120910102, 'wwqqq', 12890098733






if object_id('up_delstudentbyname','p') is not null
drop procedure up_delstudentbyname
go
create procedure up_delstudentbyname
@sname varchar(30)
as
begin
delete from T_Stuinfo
where stu_name=@sname
end


--调用


exec up_delstudentbyname 'ww'
select * from T_Stuinfo




if object_id('up_getstuinformationbyname','p') is not null
drop procedure up_getstuinformationbyname
go 
create procedure up_getstuinformationbyname
@sname varchar(30)
as
begin
select *
from T_Stuinfo "S",T_course "C",T_stu_cou "CS"
where
"S".stu_id = "CS".stu_id
and
"CS".cou_id="C".cou_id
and
stu_name=@sname
end


--调用


exec up_getstuinformationbyname 'ww' 


--2、带默认值的参数 
--编写一个存储过程up_insertstuwithdefault，给参数定义默认值，完成学生表students数据的插入
if object_id('up_insertstuwithdefault','p') is not null
drop procedure up_insertstuwithdefault
go
create procedure up_insertstuwithdefault
@sid int,
@sname varchar(30),
@sphone bigint=12347890222
as
begin
insert into T_Stuinfo(stu_id,stu_name,stu_phone)
values
(@sid,@sname,@sphone)
end


--调用
exec up_insertstuwithdefault @sid='120902054' ,@sname='给参数定义默认值'


select * from T_Stuinfo


if object_id('up_getAvgScorebyname','p') is not null
drop procedure up_getAvgScorebyname
go
create procedure up_getAvgScorebyname 
@aname varchar(30),
@avgscore int output 
as
begin
select
@avgscore=avg(cou_grade) from T_Stuinfo "S", T_stu_cou "CS"
where
"S".stu_id="CS".stu_id and "S".stu_name=@aname
end


--调用


declare @avgscoreresult int --@avgscore可以改成任意变量名
exec up_getAvgScorebyname 'ww',@avgscoreresult output
print @avgscoreresult

后续会继续更新，希望会对大家有帮助