存储过程 语法

创建带参数的存储过程
创建带参数的存储过程首先要在存储过程中声明该参数，每个存储过程参数都必须用惟一的名称进行定义。与标准的Transact-SQL变量相同，参数名必须以@为前缀，并且遵从对象标识符规则。当用户不提供该参数的值时可以使用一个默认值来代替。
在执行带参数的存储过程时，既可以通过显式指定参数名称并赋予适当的值，也可以通过提供在CREATE PROCEDURE语句中给定的参数值（不指定参数名称）来向存储过程传递值。
在存储过程PRO_Student_IN中命名4个参数，其定义顺序为@Chinese、@English、@maths和@class。
例如，将值传递给存储过程指定的参数名称。
EXEC PRO_Student_IN @class="三年一班",@Chinese=85,@maths=85,@English=85
例如，按照参数的位置传递，而不命名参数名称。
EXEC PRO_Student_IN 85,85,85,"三年一班"
1．不带默认值的参数
创建一个参数不带默认值的存储过程，在调用该存储过程时，必须对存储过程中的所有参数进行赋值，如果有一个参数没有赋值，则无法调用该存储过程。
下面创建一个不带参数默认值的存储过程，并通过指定参数的赋值，查询“MR_Student”表中指定班级的学生信息。运行结果如图1所示。

图1  创建带参数的存储过程
操作步骤如下：
（1）在操作系统的任务栏中单击“开始”菜单，选择“程序”→“Microsoft SQL Server”→“查询分析器”命令，打开查询分析器。
（2）SQL语句如下：
USE MR_SQ --打开创建存储过程的数据库
GO
--创建存储过程
CREATE PROCEDURE PRO_Student_IN
@Chinese int,   --定义参数
@English int,
@maths int,
@class varchar(20)
AS
Select *
from MR_Student
where 语文>@Chinese and 英语>@English and 数学>@maths and 班级=@class
GO
执行PRO_Student_IN存储过程有两种方法。
（1）按存储过程中参数的顺序对参数进行赋值。
EXEC PRO_Student_IN 65,77,70,"三年一班"
（2）执行存储过程时，可以按任意顺序对参数进行赋值。
EXEC PRO_Student_IN @class="三年一班",@Chinese=65,@maths=70,@English=77
2．带默认值的参数
在执行存储过程PRO_Student_IN时，如果缺省任何一个参数，将会产生错误，解决这种问题的方法是建立使用默认值的参数。只要在参数的定义之后加上等号，并在等号后面写出默认值。
例如，给参数设置默认值。
@Str varchar(20)='长春',
@Int Int=10,
@Char char(8)='0001',
@float float=12.3
本示例利用存储过程中的默认参数对数据表“MR_Student”进行查询。运行结果如图2所示。

图2  创建带参数默认值的存储过程
操作步骤如下：
（1）在操作系统的任务栏中单击“开始”菜单，选择“程序”→“Microsoft SQL Server”→“查询分析器”命令，打开查询分析器。
（2）SQL语句如下：
--创建存储过程
USE MR_SQL
GO
CREATE PROCEDURE PRO_Student_NULL
@Chinese int=60,    --对参数设置默值
@English int=60,
@maths int=60,

@class varchar(20)='三年二班'

AS
Select *
from MR_Student
where 语文>@Chinese and 英语>@English and 数学>@maths and 班级=@class
GO
执行存储过程，查询三年二班成绩全部及格并且数学成绩大于80的学生信息。
EXEC PRO_Student_NULL @maths=80
也可以用以下SQL语句调用PRO_Student_NULL存储过程：
例如，查询三年二班成绩全部及格并且语文成绩大于70的学生信息。
EXEC PRO_Student_NULL 70
例如，查询三年二班成绩全部及格的学生信息。
EXEC PRO_Student_NULL
3．带返回参数的存储过程
在创建存储过程时，可以用OUTPOT参数来创建一个带返回值的存储过程，例如：
@int int OUTPUT
@char char(10) OUTPUT
@float float OUTPUT
其中text、image等大于二进制数据类型的参数不能作为OUTPUT参数，在调用存储过程时，不必为返回参数赋值。
本示例利用存储过程中的OUTPUT参数，在调用存储过程后利用返回值来判断查询的结果。运行结果如图3所示。

图3  创建带返回参数的存储过程
操作步骤如下：
（1）在操作系统的任务栏中单击“开始”菜单，选择“程序”→“Microsoft SQL Server”→“查询分析器”命令，打开查询分析器。
（2）SQL语句如下：
--创建带返回值的存储过程
CREATE PROCEDURE PRO_OUP
@class char(10),
@ave int OUTPUT    --设置带返回值的参数
AS
select @ave =avg(数学) from MR_Student where 班级=@class
GO
--执行存储过程
DECLARE @average int    --自定义变量
DECLARE @subject char(10)
SET @subject='三年一班'    --对变量进行赋值
EXEC PRO_OUP @subject,@average output    --调用存储过程
if @average>=85    --利用存储过程的返回值进行判断
PRINT rtrim(@subject)+'数学的平均成绩为高等水平'
if @average>=70
PRINT rtrim(@subject)+'数学的平均成绩为中等水平'
if @average<70
PRINT rtrim(@subject)+'数学的平均成绩为一般水平'
运行结果：
三年一班数学的平均成绩为79，属于中等水平