T-SQL 无参数的存储过程的创建和执行

 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_ScoreQuery')
 4 drop procedure usp_ScoreQuery
 5 go
 6 create procedure usp_ScoreQuery --创建存储过程
 7 as
 8     --查询考试信息
 9     select Students.StudentId,StudentName,ClassName,
10               ScoreSum=(CSharp+SQLServerDB) from Students
11     inner join StudentClass on StudentClass.ClassId=Students.ClassId
12     inner join ScoreList on Students.StudentId=ScoreList.StudentId
13     order by ScoreSum DESC
14     --统计分析考试信息
15     select StudentClass.ClassId,C#Avg=avg(CSharp),DBAvg=avg(SQLServerDB)  into #scoreTemp
16     from StudentClass 
17     inner join Students on StudentClass.ClassId=Students.ClassId
18     inner join ScoreList on ScoreList.StudentId=Students.StudentId
19     group by StudentClass.ClassId order by ClassId
20     select ClassName,C#Avg,DBAvg from #scoreTemp
21     inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId
22 go
23 exec usp_ScoreQuery  --调用存储过程

这里的  into #scoreTemp  是把查询的数据放入到临时表中

 

转载于:https://www.cnblogs.com/Spinoza/p/10051116.html