如何在VIEW（SQL Server 2012）中动态设置参数，然后在存储过程中调用视图？

问题描述

这是观点。

创建视图[dbo]。[View_Sum_Student_Marks] 
 AS 
 SELECT TOP（100）PERCENT StudentID，SUM（Scie + Maths + Engl + Sets + Agri + SStud + Moral + Gen + Pract）AS Total，
 CAST（ROUND（SUM（Scie + Maths + Engl + Sets + Agri + SStud + Moral + Gen + Pract）/ 9,2）AS decimal（12,2））AS Average 
 FROM dbo.student_results 
 WHERE（ExamID = 2）AND（AcademicYear = 2017）
 GROUP BY StudentID 
 ORDER BY Total DESC 

我想动态设置 ExamID和AcademicYear 然后我想通过如下所示的程序调用视图

 ALTER PROCEDURE [dbo]。[proc_Top50Form1] 
  - 在此处添加存储过程的参数
 
 @examid int，
 @academicyear int 
 AS 
 BEGIN 
  - 添加SET NOCOUNT ON以防止
的额外结果集 - 干扰SELECT语句。 
 SET NOCOUNT ON; 
 
 SELECT TOP（100）PERCENT dbo.students.StudentID，dbo.students.Fistname，dbo.students.Lastname，dbo.students.Gender，dbo.students_overall_analysis.Points，
 dbo。 students_overall_analysis.Grade，dbo.View_Sum_Student_Marks.Total AS [Subjects Total]，dbo.View_Sum_Student_Marks.Average AS [Subjects Average]，
 students_overall_analysis.AcademicYear，dbo.classes.ClassName，exam_types.ExamType 
 FROM dbo。 View_Sum_Student_Marks 
 INNER JOIN 
 dbo.students ON dbo.View_Sum_Student_Marks.StudentID = dbo.students.StudentID 
 INNER JOIN 
 dbo.classes ON dbo.students.AcademicClass = dbo.classes。 ID 
 INNER JOIN 
 dbo.students_overall_analysis ON dbo.students.StudentID = dbo.students_overall_analysis.StudentID 
 INNER JOIN exam_types ON students_overall_analysis.ExamID = exam_types.ID 
 WHERE（dbo.students_overall_analysis .ExamID = @examid）AND（dbo.students_overall_analysis.Aca demicYear = @academicyear）
 AND（classes.ClassName LIKE'％1％'）
 ORDER BY dbo.students_overall_analysis.Points DESC，[Subjects Total] DESC 

我尝试过的事情：



伙计们，请你们帮助我。我正在研究学校管理系统而且我很困难并且需要帮助。

解决方案

这篇关于如何在VIEW（SQL Server 2012）中动态设置参数，然后在存储过程中调用视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！