任务10.3   存储过程参数和返回值

一 、指定默认参数

     在存储过程中，参数可以用来向存储过程中输入值，也可以从存储过程中输出值。下而将介绍在存储过程中使用参数的内容。

    在创建存储过程时，可以通过为参数指定默认值，而创建一个带有可选参数的存储过程。在执行该存储过程中，如果不传递该参数，则使用默认值。

     如果在创建存储过程时没有为参数指定默认值，并且在调用该存储过程的程序中也没有为该参数提供值，那么系统会返回错误信息。因此指定参数的默认值是有必要的。

二 、指定参数方向

       当调用程序执行存储过程时，所有过程参数都可以接收输入值。如果在存储过程中为某个参数指定OUTPUT选项，则存储过程将在执行完毕退出时向调用程序返回该参数的当前值。调用程序在调用该存储过程时，也必须使用OUTPUT关键字指定相对应的实参，并且该实参必须是一个变量而不能是一个常量，这样才能将该参数的返回值保存到变量中以便在调用程序中使用。

三 、使用RETURN语句

RETURN语句用于退出存储过程，返回到调用程序继续执行。

在【例】中已经看到了RETURN语句的使用，它是一个常用的使用RETURN语句返回分支的例子。可以看到，当存储过程遇到RETURN语句后立即返回，而后续的语句将不被执行。

在存储过程中可以使用RETURN语句返回一个被称为返回代码的整数值，以表明存储过程的状态。通常来说，返回0值代表成功，非0值代表失败。RETURN不能返回空值(null)，如果某个过程试图返回空值(例如，使用RETURN@status，而@status为NULL)，则将生成警告消息并返回O值。

调用程序在执行存储过程时，与OUTPUT参数一样，必须将返回代码保存到一个变量中，以便在调用程序中查看和使用该返回代码值。

例：带输入参数的存储过程

【例】在“学生成绩管理系统”的[教务数据库]中创建一个存储过程，该存储过程返回某年级某性别的学生信息。该存储过程接收两个输入参数，分别对应专业班级和性别，可在【查询编辑器】窗口中输入如下代码：

　　USE [教务数据库]

　　GO

　　CREATE PROCEDURE GetStuInfoByCon @Grade char(4),@Sex nchar(1)

　　AS

　　  SELECT * From  学生表

　　  WHERE 专业班级=@Grade AND 性别=@Sex

查阅存储过程程序代码

EXEC sp_helptext  存储过程名

例：

 EXEC sp_helptext  GetStuInfoByCon

存储过程的创建与执行

单击工具栏中【执行】按钮，创建该存储过程。

存储过程创建成功后，执行存储过程：

　　EXECUTE  GetStuInfoByCon  ‘2011’, ‘女’

　　　执行结果如图9.2所示：

以下命令的执行结果与上面相同（年级改为2019）：

　　EXECUTE GetStuInfoByCon  @Grade=‘2019’,@Sex= ‘女’

　　或者如下：

　　DECLARE @ProcStu VarChar(20)

　　SET @ProcStu =‘GetStuInfoByCon’

　　EXECUTE @ProcStu  @Grade=‘2019’,@Sex= ‘女’

例：输入参数使用默认值的存储过程

【例】在“学生成绩管理系统”的[教务数据库]中创建一个存储过程，该存储过程返回根据姓氏查询得到的学生信息，如果未提供查询的姓氏，则默认查询姓张的学生信息。可在【查询编辑器】窗口中输入如下代码并执行：

　　USE 教务数据库

　　GO

　　CREATE PROCEDURE SerachStuInfo  @SurName NVarchar(2)=‘张’

　　AS

　　  SELECT * FROM  学生表

　　  WHERE 姓名 LIKE @SurName+’%’

执行该存储过程：

　EXECUTE SerachStuInfo ‘李’

执行结果如图（下）所示：

不带参数执行该存储过程：

　　EXECUTE SerachStuInfo

执行结果如图所示：

使用输入参数

通过位置传递参数

在执行存储过程的语句中直接给出参数的值。当有多个参数时，给出的参数值的顺序与创建存储过程的语句中的参数顺序一致，即参数传递的顺序就是参数定义的顺序。

通过参数名传递参数

在执行存储过程的语句中，用“参数名=参数值”的形式给出参数值。

使用输出参数

通过定义输出参数，可以从存储过程中返回一个或多个值。定义输出参数需要在参数定义后加OUTPUT关键字。如下图所示。

例：带输出参数的存储过程

【例】在“学生成绩管理系统”数据库中创建一个存储过程，该存储过程根据专业班级返回学生人数。可在【查询编辑器】窗口中输入如下代码并执行：

　　USE  教务数据库

 GO

　　CREATE PROCEDURE GetNumByGrade @Grade Char(12),@Num INT OUTPUT

　　AS

　　  SELECT @Num = COUNT(*)  FROM  学生表

          WHERE 专业班级=@Grade

执行该存储过程：

DECLARE @StuNum int

EXECUTE GetNumByGrade '19软件5班',@StuNum OUTPUT

SELECT '19软件5班'  专业班级,@StuNum  人数

执行结果如图所示：

通过RETURN返回值

在存储过程中除了可以返回输出参数以外，还可以有返回值，用来显示存储过程的执行情况。如下图所示。

例：创建存储过程：根据书号，查询某书是否借出。

CREATE  PROCEDURE  IsBorrowed

  @BookID int 

   AS

      DECLARE @BookState bit

       SELECT @BookState=是否借出

         FROM Books

           WHERE 图书编号 = @BookID 

              RETURN @BookState

--调用存储过程并获得返回值：

DECLARE @IsBorrowed bit

EXEC @IsBorrowed=IsBorrowed 1

IF @IsBorrowed=0

  PRINT '未借出'

ELSE

  PRINT  '已借出'