创建
use StudentManager go if exists(select * from sysobjects where name='usp_ScoreQuery4') drop procedure usp_ScoreQuery4 go create procedure usp_ScoreQuery4 --创建带参数的存储过程 @AbsentCount int output,--缺考总人数 @FailedCount int output,--不及格总人数 @CSharp int=60, @DB int=60 as select Students.StudentId,StudentName,C#=CSharp,DB=SQLServerDB from Students inner join ScoreList on Students.StudentId=ScoreList.StudentId where CSharp<@CSharp or SQLServerDB<@DB --显示结果列表 select @AbsentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList) --查询缺考总人数 select @FailedCount=count(*) from ScoreList where CSharp<@CSharp or SQLServerDB<@DB --查询不及格总人数 go
执行
1 use StudentManager 2 go 3 --调用带参数的存储过程 4 declare @AbsentCount int,@FailedCount int --首先定义输出参数 5 exec usp_ScoreQuery4 @AbsentCount output,@FailedCount output 6 --使用反馈的结果 7 select 缺考总数=@AbsentCount,不及格总数=@FailedCount