例如:
创建一个名为stu_proc1的存储过程,查询某系、某姓名的学生的学号、姓名、年龄,选修课程名、成绩。系名和姓名在调用该存储过程时输入,其默认值分别为“%”与"0%”。执行该存储过程,用多种参数加以测试。
if exists (select * from sysobjects where name='stu_proc1' and type='P')
begin
drop procedure stu_proc1 print '已删除!'
end
else
print '不存在,可创建! '
执行结果:
创建语句:
create procedure stu_proc1
@sdept varchar(10)='%', @sname varchar(10)='0%'
as
select Sname , s.Sno, YEAR(getdate ( ) ) -YEAR(Birth) Age, Cname ,Grade from student s, Course c,sc
where s.Sno=sc.sno and c.Cno=sc.Cno
and s.Sname like @sname and s.sdept like @sdept
执行结果:
执行已经创建好的存储过程
执行1:
exec stu_proc1
执行2:
exec stu_proc1 @sdept='%', @sname ='0%'
(2)创建一个名为Student_sc的存储过程,可查询出某段学号的同学的学号、姓名、总成绩。(学号起始号与终止号在调用时输入,可设默认值)。执行该存储过程。
if exists (select name from sysobjects where name='student_sc' and type='P')
drop procedure student_sc
go
create procedure student_sc
@sno_begin varchar (10)='20110001 ',@sno_end varchar (10)='20110103'
as
select s.Sno,Sname , SUM (grade) total_grade from student s,sc
where s.sno=sc.sno and s.sno between @sno_begin and @sno_end
group by s.sno ,sname
执行:
exec student_sc