【SQL Server】存储过程

use StudentManager
go
if exists(select * from sysobjects where name ='usp_ScoreQuery2')
drop procedure usp_ScoreQuery2
go
--创建带参数的存储过程
create procedure usp_ScoreQuery2
@CSharp int,
@DB int 
as 
    select Students.StudentId,StudentName,CSharp ,SQLServerDB from Students 
    inner join ScoreList on Students.StudentId = ScoreList.StudentId
    where CSharp <@CSharp or SQLServerDB<@DB
go
--调用带参数的存储过程
exec usp_ScoreQuery2 60,65 --按照参数顺序赋值

use StudentManager
go
if exists(select * from sysobjects where name ='usp_ScoreQuery3')
drop procedure usp_ScoreQuery3
go
--创建带参数的存储过程
create procedure usp_ScoreQuery3
--out一般写在输入参数之前
@ASentCount int output,--缺考人数
@FailedCount int output,--不及格总人数
@CSharp int=60,
@DB int=60 
as 
    select Students.StudentId,StudentName,CSharp ,SQLServerDB from Students 
    inner join ScoreList on Students.StudentId = ScoreList.StudentId
    where CSharp <@CSharp or SQLServerDB<@DB

    --查询统计结果
    select @ASentCount=count(*) from Students
    where StudentId not in (Select StudentId from ScoreList) --查询缺考的人数
    select @FailedCount=count(*) from ScoreList
    where CSharp<@CSharp or SQLServerDB<@DB --查询不及格的总人数

go
--调用带输出参数的存储过程
declare @ASentCount int,@FailedCount int --首先定义输出参数
exec usp_ScoreQuery3 @ASentCount output,@FailedCount output
select @ASentCount 缺考人数,@FailedCount 不及格人数