创建带参数的存储过程首先要在存储过程中声明该参数,每个存储过程参数都必须用惟一的名称进行定义。与标准的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所示。
![](http://www.isstudy.com/d/file/uploads/allimg/091013/1_091013155013_1.gif)
图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所示。
![](http://www.isstudy.com/d/file/uploads/allimg/091013/1_091013155001_1.gif)
图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,属于中等水平