创建存储过程

一、定义存储过程的语法

语法：

CREATE PROC[EDURE] 存储过程名

　　@参数1 数据类型 = 默认值 OUTPUT,

　　......

　　@参数n 数据类型 = 默认值 OUTPUT

AS

　　SQL语句

GO

 

存储过程的参数：

• 和C#语言的方法一样，参数可选
• 参数分为输入参数、输出参数
• 输入参数允许有默认值

 

二、自定义存储过程实例

1、需求

在StudentManageDB数据库里有如下三张表，分别是学生表，成绩表，班级表：

 

 现在有一个需求：查询考试成绩，显示学号、姓名、班级、总成绩、并按成绩的总分高低排序。

可以使用存储过程来解决：

 

2、新建存储过程

use StudentManageDB
go
if exists(select * from sysobjects where name = 'usp_ScoreQuery')
drop procedure usp_usp_ScoreQuery
go
create procedure usp_ScoreQuery
as
    --查询考试成绩
    select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp + SQLServerDB) from Students
    inner join StudentClass on StudentClass.ClassId=Students.ClassId
    inner join ScoreList on Students.StudentId=ScoreList.StudentId
    order by ScoreSum desc
go

 

执行完毕，就可以在如下菜单中看到新建的存储过程了：

 

3、调用存储过程

exec usp_ScoreQuery

效果如下：

 

 

4、更新存储过程

现在有一个新的需求，统计考试成绩，显示班级名称、C#平均分、数据库平均分，按照班级分组实现。

4.1、更新

use StudentManageDB
go
if exists(select * from sysobjects where name = 'usp_ScoreQuery')
drop procedure usp_usp_ScoreQuery
go
create procedure usp_ScoreQuery
as
    --查询考试成绩
    select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp + SQLServerDB) from Students
    inner join StudentClass on StudentClass.ClassId=Students.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 ClassName
go

 

4.2、调用

三、带参数的存储过程

use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery2')
drop procedure usp_ScoreQuery2
go
--创建带参数的存储过程
create procedure usp_ScoreQuery2
@CSharp int=60,
@DB int=60
as
    select Students.StudentId, StudentName,C#=CSharp,DB=SQLServerDB from Students
    inner join ScoreList on Students.StudentId=ScoreList.StudentId
    where CSharp<@CSharp or SQLServerDB<@DB
go

--调用带参数的存储过程
exec usp_ScoreQuery2 60,65 --按照参数顺序赋值
exec usp_ScoreQuery2 @DB=65,@CSharp=60 --参数顺序可以调换
exec usp_ScoreQuery2 65  --第二个参数是默认值了
exec usp_ScoreQuery2 default,65 --第一个参数是默认值了
exec usp_ScoreQuery2 --俩个参数都是默认值了