存储过程参数与状态值

       存储过程和调用者之间通过参数交换数据，可以按输入的参数执行，也可由参数输出执行结果。调用者通过存储过程返回的状态值对存储过程进行管理。

1.参数

存储过程的参数在创建过程时声明。SQL Server支持两类参数：输入参数和输出参数。

1）输入参数

输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须在CREATE PROCEDURE语句中声明一个或多个变量及类型。

举例：创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量登信息。

CREATE PROC sell_info @employee_name varchar(20) AS

SELECT employee_name,goods_name,classification_name,order_num

FROM employee e INNER JOIN sell_order s ON e.employee_id=g.classification_id

WHERE employee_name LIKE @employee_name

存储过程sell_info以@employee_name变量作为输入参数，执行时，可以省略参数名，直接给参数值。在SQL查询分析其中输入命令：

EXEC sell_info‘东方木’

运行结果如下图所示：

带参数存储过程的执行结果

	employee_name	goods_name	classification_name	order_num
1	东方木	IBM R51	笔记本计算机	40
2	东方木	HP1020	激光打印机	10

参数值可以包含通配符”%“，例如，查找所有姓”钱“的员工的销售情况可以使用以下命令：

EXEC sell_info ‘钱%’

如果由多个输入参数的话，应该为某一个输入参数提供默认值，参数的默认值必须为常量或NULL。执行输入参数带默认值的存储过程时，可以不为该参数指定值。

执行时，参数可以由位置标识，也可以由名字标识。如果以名字传递参数，则参数的顺序是任意的。名字应该尽量选用具有意义的，以帮助用户和程序员传递合适的值。

例如，定义一个具有3个参数的存储过程：

CREATE PRCO myproc @vall int,@val2 int,@val3 int

AS...

以下命令中参数以位置传递，参数的赋值以其在CREATE PROCEDURE语句中定义的顺序进行：

EXEC myproc 10,20,15

以下命令以名字传递参数，每个值由对应的参数名引导：

EXEC myproc @val2=20,@vall=10,@val3=15

按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递传参数却具有更快的速度。

2）输出参数

输出参数允许存储过程将数据值或游标变量传回调用程序。OUTPUT关键字用以指出能返回到调用它的批处理或过程中的参数。为了使用输出参数，在CREATE PROCEDURE和EXECUTE语句中都必须使用OUTPUT关键字。

举例：创建存储过程price_goods，通过输入参数在goods表中查找商品，以输出参数获取商品单价。

CREATE PROCEDURE price_goods @goods_name varchar(80)=NULL,

                                                        @price_goods real OUTPUT

AS

SELECT @price_goods=unit_price

FROM goods

WHERE goods_name=@goods_name

本例中，输入参数为@goods_name变量，在执行时将商品名称传递给过程price_goods。输出参数为@price_goods变量，在执行后将商品名为@goods_name的商品单价返回给调用程序的变量。因此EXECUTE语句需要一个已声明的变量以存储返回的值（如@price），变量的数据类型应当同输出参数的数据类型相匹配。EXECUTE语句还需要关键字OUTPUT以允许参数值返回给变量。执行price_goods存储过程的代码如下：

/*先定义变量，商品单价按变量的位置返回*/

DECLARE @price real

EXEC PRICE_GOODS 'Canon LBP2900',@price OUTPUT

SELECT @price

运行结果是商品名为”Canon LBP2900“的商品单价：

1380

存储过程输入的参数值不同，获取的输出结果也不同，这样存储过程可以多次被用户调用以满足用户的铲鲟需求。

2.返回存储过程的状态

1）用RETURN语句定义返回值

存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。如果存储过程没有显式设置返回代码的值，则SQL Server返回代码为0，表示成功执行；若返回-1~-99之间的整数，表示没有成功执行。也可以使用RETURN语句，用大于0或小于-99的整数来定义自己的返回状态值，以表示不同的执行结果。

在建立过程的时候，需要定义出错条件并把他们与整型的出错代码联系起来。

举例：创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，用值15表示用户没有提供参数；值-101便是没有输入商品类别；值0便是过程运行没有出错。

/*存储过程在出错时设置出错状态*/

CREATE PROC cl_goods @cl_name varchar(40)=NULL

AS

IF @cl_name=NULL

RETURN 15

IF NOT EXISTS

(SELECT * FROM goods_classification WHERE classification_name=@cl_name)

RETURN -101

SELECT g.goods_name FROM goods_classification gc,goods g

WHERE gc.classification_id=g.classification_id

            AND gc.classification_name=@cl_name

RETURN 0

2)捕获返回状态值

在执行过程时，要正确接受返回的状态值，必须使用以下语句：

EXECUTE @status_var=procedure_name

其中，@status_var变量应在EXECUTE命令之前声明。它可以接受返回的状态码。如此，当存储过程自行出错时，调用它的批处理或应用程序将会采取相应的措施。

上例的存储过程cl_goods执行时使用以下语句。

/*检查状态并报告出错原因*/

DECLARE @return_status int

EXEC @return_status=cl_goods '笔记本计算机'

IF @return_status=15

SELECT '语法错误'

ELSE

IF @return_status=-101

SELECT '没有找到该商品类型'

执行时，将对不同的输入值返回不同的状态值及处理结果。

除了用户定义的状态码以外，如果存储过程在运行中异常中止，SQL Server提供了相应的出错代码。