studentID studentName courseName1 courseName2 courseName3 ……
1 lupenda 66 77 88 ……
。
。
。
下面的例子就是为了实现上面的功能，该存取过程涉及3张表：
Course表：
CourseID，CourseName,Duration,[Year],MajorID,Semester
StudentData表：
StudentID,StudentName,ClassID……
Student_Course表：
StudentID，CourseID，Score
--根据不同参数取得成绩
--可以得到某学期，某专业，某班级，某学生的成绩
CREATE procedure GetScore
@Year int ,@Semester bit ,@MajorID int = null,@ClassID int = null,@StudentID int = null
as
declare @var varchar(200)
declare @sql varchar(8000)
set @sql=‘’‘’
--放进临时表
select distinct CourseName into #tb from Course where Course.Year = @Year and Course.Semester = @Semester
--声明游标
declare cur cursor
for select CourseName from #tb
open cur
fetch next from cur into @var
while @@fetch_status=0
begin
set @sql = @sql + ‘’sum(case when Course.CourseName = ‘’‘’‘’ +@var+ ‘’‘’‘’ then Student_Course.Score else 0 end) as ‘’+ @var+‘’,‘’
fetch next from cur into @var
end
set @sql = left(@sql,len(@sql)-1)
set @sql = ‘’select Class.ClassName as 班级,Student_Course.StudentID as 全学号,StudentData.StudentName as 姓名, ‘’ +@sql+ ‘’
from StudentData,Student_Course,Course ,Class
where Student_Course.CourseID = Course.CourseID
and StudentData.StudentID = Student_Course.StudentID
and StudentData.ClassID = Class.ClassID‘’
--根据输入参数的不同创建查询语句
if @Year is not null
set @sql = @sql+ ‘’ and Course.Year = ‘’+ Cast( @Year as varchar(4)) --强制转换为字符串
if @Semester is not null
set @sql = @sql+ ‘’ and Course.Semester = ‘’+ Cast( @Semester as varchar(1))
if @MajorID is not null
set @sql = @sql+ ‘’ and Course.MajorID = ‘’+ Cast( @MajorID as varchar(4))
if @ClassID is not null
set @sql = @sql+ ‘’ and StudentData.ClassID = ‘’+ Cast( @ClassID as varchar(4))
if @StudentID is not null
set @sql = @sql+ ‘’ and StudentData.StudentID = ‘’+ Cast( @StudentID as varchar(8))
set @sql = @sql+ ‘’group by Class.ClassName,Student_Course.StudentID,StudentData.StudentName order by Student_Course.StudentID ‘’
exec(@sql)
deallocate cur
drop table #tb
GO
至此，该存取过程实现了我们想要的功能.第一次发文章，请大家多多支持！
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
