C#--存储过程综合查询的编写

存储过程的综合查询

根据班级查询
根据全校查询
根据考试成绩查询
根据缺考人员查询
使用存储过程可以一个方法查询多个

---

编写数据库存储过程

基本框架

存储过程语句

use StudentManageDB
go

if exists(Select * from sysobjects where name='usp_ScoreQuery')
drop procedure usp_ScoreQuery
go
create procedure usp_ScoreQuery
@ClassName varchar(50),@stuCount int output,@avgCSharp int output,
@avgDB int output,@absentCount int output
as
    if(len(@ClassName)=0)--[查询全部]
        begin           
            --查询全校考试成绩列表
            Select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB
            from Students
            inner join ScoreList on Students.StudentId=ScoreList.StudentId
            inner join StudentClass on Students.ClassId = StudentClass.classId
            --查询考试统计信息
            select @stuCount=Count(*),@avgCSharp=avg(CSharp),@avgDB=avg(SQLServerDB)
            from ScoreList
            select @absentCount=Count(*)from Students where StudentId not in 
            (Select StudentId from ScoreList)
            --查询没有参加考试的学生名单
            Select StudentName from  Students where StudentId not in(Select StudentId