SQLServer -- 自定义无参数存储过程

自定义储过程

use StuManageDBgoif exists(Select * from sysobjects where name='usp_ScoreQuery')drop procedure usp_ScoreQuerygocreate procedure usp_ScoreQuery --创建存储过程as    --查询考试信息    select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp+SQLServerDB)    from Students    inner join StudentClass on Students.ClassId = StudentClass.ClassId    inner join ScoreList on Students.StudentId = ScoreList.StudentId    order by ScoreSum DESCgoexec usp_ScoreQuery

---

---

多张表的储存过程

use StuManageDBgoif exists(Select * from sysobjects where name='usp_ScoreQuery')drop procedure usp_ScoreQuerygocreate procedure usp_ScoreQuery --创建存储过程as    --查询考试信息    select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp+SQLServerDB)    from Students    inner join StudentClass on Students.ClassId = StudentClass.ClassId    inner join ScoreList on Students.StudentId = ScoreList.StudentId    order by ScoreSum DESC    --查询班级平均分    select ClassName,C#Avg=Avg(CSharp),DBAvg=Avg(SQLServerDB)    from ScoreList    inner join Students on Students.StudentId = ScoreList.StudentId    inner join StudentClass on StudentClass.ClassId = Students.ClassId    group by ClassName order by ClassNamegoexec usp_ScoreQuery

---

存储过程临时表

在处理数据过程中临时保存一些数据。

use StuManageDBgoif exists(Select * from sysobjects where name='usp_ScoreQuery')drop procedure usp_ScoreQuerygocreate procedure usp_ScoreQuery --创建存储过程as    --查询考试信息    select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp+SQLServerDB)    from Students    inner join StudentClass on Students.ClassId = StudentClass.ClassId    inner join ScoreList on Students.StudentId = ScoreList.StudentId    order by ScoreSum DESC    --使用临时表    select StudentClass.ClassId,c#Avg=Avg(CSharp),DBAvg=Avg(SQLServerDB)    into #ScoreTemp from ScoreList     inner join Students on Students.StudentId=ScoreList.StudentId    inner join StudentClass on StudentClass.ClassId = Students.ClassId    group by StudentClass.ClassId order by StudentClass.ClassId    --将临时表和班级查询表关联    select ClassName,c#Avg,DBAvg from #ScoreTemp    inner join StudentClass on StudentClass.ClassId=#ScoreTemp.ClassIdgoexec usp_ScoreQuery