(1)不带参数：
use studb
go
if exists (select * from sysobjects where name ='proc_stu')
drop procedure proc_stu
go
create procedure proc_stu
as
declare @writtenAvg float,@labAvg float
select @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) from stuMarks
print '笔试平均分： ' + convert(varchar(5),@writtenAvg)
print '机试平均分:' +convert(varchar(5),@labAvg)
if(@wirttenAvg>70 and labAvg>70)
print '本班成绩：优秀'
else
print '本班成绩:较差'
select stuname,stuinfo.stuno,writtenExam,labExam fomr stuinfo innner join stuMarks on stuinfo.stuno=stuMarks.sutno where writtenExam<60 or stuExam<60
go
exec proc_stu
(2)创建带输入参数的存储过程
use studb
go
if exists(select * from sysobjects where name='proc_stu')
drop procedure proc_stu
go
create procedure proc_stu
@writtenPass int=60,
@labpass int=60
as
select stuname,stuinfo.stuno,writtenExam,labExam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno where writtenExam<@writtenPass or labExam<@labpass
go
exec proc_stu 相当于（exec proc_stu 60,60）
exec proc_stu 64 相当于（exec proc_stu 64,60）
exec proc_stu 60,50
exec proc_stu @labpass=55,@writtenpass=65 相当于（exec proc_stu 65,55）
(3)创建带输出参数的存储过程
use studb
go
if exists (select * from sysobjects where name='proc_stu')
drop procedure proc_stu
go
create procedure proc_stu
@notpasssum in output,
@writtenpass int=60,
@lapass int=60
as
select stunam,stuinfo.stuno,writtenexam,labexam from stuinfo inner join stuMarks on stuinfo.stuno=stumarks.stuno where writtenexam<@writtenpass or labExam<@labpass
select @notpasssum=count(stuno) from stumarks where writtenexam<@writtenpass or labExam<@labpass
go
declare @sum int
exec proc_stu @sum output,64
if
@sum>=3
print '未通过人数:' +convert(varchar(5),@sum) + '人'
else
print '为通缩人数:' +convert(carchar(5),@sum) + '人'
go