执行存储过程

12.3.3 执行存储过程

当需要执行存储过程时，可使用EXECUTE或EXEC 语句。如果存储过程是批处理中的第一条语句，那么不使用EXECUTE 或EXEC关键字也可以执行该存储过程。

如果存储过程编写为可以接受参数值，那么可以提供参数值。

任务12.4

问题描述：执行任务12.3创建的存储过程Insertproduct，向产品表中追加一行。

解决方案1：

EXEC Insertproduct 8,'Konbu', '2 kg box', 24,6.0000, 5,10,0

解决方案2：

EXEC Insertproduct 8,'Konbu', '2 kg box', 24,6.0000, 5,10

解决方案3：

EXEC Insertproduct default,'Konbu', '2 kg box', 24,6.0000, 5,10

解决方案4：

EXEC Insertproduct @ProductName='Konbu',@QuantityPerUnit='2 kg box',

@UnitsInStock=24,@UnitPrice=6.0000,@UnitsOnOrder=5,@ReorderLevel=10

解决方案5：

EXEC Insertproduct @ReorderLevel=10, @UnitsOnOrder=5,

@UnitsInStock=24,@UnitPrice=6.0000, @QuantityPerUnit='2 kg

box',@ProductName='Konbu'

分析与讨论：

1.如果存储过程含有参数，则在执行存储过程时，必须为这些参数提供值（具有默认值的参数可以不提供值），而且提供的值必须是确定的值，例如方案1。并且必须按照CREATE PROCEDURE 语句中存储过程参数给出的顺序提供参数值，以下语句就会发生错误，因为不是按照顺序提供参数值。

EXEC Insertproduct 'Konbu',8,'2 kg box', 24,6.0000, 5,10,0

2.如果存储过程含有默认值参数，则在执行存储过程时，可省略为默认值参数提供值，但其它参数的值不能够省略，必须为这些参数提供值。例如方案2，没有为@Discontinued 参数提供值，则在执行存储过程时该参数的值为其默认值1.

3.虽然可以省略已提供默认值的参数，但只能截断参数列表。例如，如果一个存储过程有八个参数，可以省略第七个和第八个参数，但不能跳过第七个参数而仍然包含第八个参数，除非以@parameter = value 形式提供参数（参见4）。例如，方案2中，@CategoryID参数尽管是有默认值的参数，但由于它是第一个参数，而存储过程第二个参数必须提供值，因此执行存储过程时要为@CategoryID参数提供值，不能省略。如果要使用默认值作为参数的值，则可以使用DEFAULT关键字，例如方案3。

如果在存储过程中定义了参数的默认值，那么下列情况下将使用默认值：

1）执行存储过程时未指定参数值。

2）将DEFAULT 关键字指定为参数值。

4. 执行存储过程时，既可以通过提供在CREATE PROCEDURE 语句中给定的参数值（如解决方案1，解决方案2），来向存储过程参数传递值。也可以通过显式指定参数名称并分配适当的值（使用@parameter = value 的形式），来给存储过程参数传递值。

如果使用@parameter = value 的形式，那么可以按任意顺序提供参数，还可以省略那些已提供默认值的所有参数。注意，如果以@parameter = value 形式提供了一个参数，就必须按此种形式提供后面所有的参数。例如解决方案4和解决方案5，执行存储过程时存储过程@ProductName参数的值为'Konbu'，@QuantityPerUnit参数的值为'2 kg box', @UnitsInStock参数的值为24,@UnitPrice参数的值为6.0000,@UnitsOnOrder参数的值为

5,@ReorderLevel参数的值为10，没有提供@CategoryID和@Discontinued参数的值，它们的值为其默认值。

执行存储过程时，如果参数名称和存储过程的参数名称不匹配，则这些参数都不会被接受。例如以下代码参数名称@ReorderLevelA和存储过程的参数名称不匹配，则参数@ReorderLevelA不会被接受。

EXEC Insertproduct @ReorderLevelA=10, @UnitsOnOrder=5,

@UnitsInStock=24,@UnitPrice=6.0000, @QuantityPerUnit='2 kg

box',@ProductName='Konbu'

执行存储过程时，服务器将拒绝所有未包含在存储过程创建期间的参数列表中的参数。

注意，在执行存储过程时指定参数名称允许按任意顺序提供参数值。如果未指定参数名称，则必须按照参数在存储过程中定义时的顺序（从左至右）来提供参数值（按照位置传递参数值），并且，如果给某一参数提供了值，则该参数前面的所有参数必须为它们提供值，即使这些参数有默认值（如方案2）。

12.4.1 使用变量、IF语句和RETURN语句

变量用来存储程序在运行过程中可以使用的值，用户可以定义变量，用户定义的变量称为局部变量。

IF语句根据表达式的值选择要执行的语句。IF语句的一般表示形式为：

IF (表达式)

BEGIN

语句块;

END

IF语句的执行方式如下：如果表达式的值为TRUE，则执行语句块，否则语句块不会被执行。语句块可为一条或多条语句，如果句块可为一条语句，可省略BEGIN、END。

任务12.6

问题描述：创建一个存储过程，用参数指定的产品类别ID的值查询该类的所有产品，并测试错误，如果存储过程成功执行，则存储过程返回0，如果执行存储过程时，执行存储过程中的SQL 语句发生错误，则存储过程返回返回错误号。并创建代码测试该程序过程。

解决方案：

1）创建ProductsByCategory1存储过程

CREATE Procedure ProductsByCategory1

(

@CategoryID int

)

AS

DECLARE @ErrorSave int;

SET @ErrorSave = 0;

SELECT * FROM Products

WHERE CategoryID = @CategoryID

ORDER BY ProductName;

IF (@@ERROR <> 0)

BEGIN

SET @ErrorSave = @@ERROR;

END

RETURN @ErrorSave;

GO

2）创建测试代码

DECLARE @ReturnStatus int;

EXECUTE @ReturnStatus =ProductsByCategory1 1;

SELECT'Return code = ' + CAST(@ReturnStatus AS CHAR(10));

GO

分析与讨论：

1．声明变量。使用DECLARE 语句声明变量，其格式如下：

DECLARE 变量名数据类型和长度

变量名的第一个字符必须为一个@。声明变量后变量的值初始化为NULL。

变量不能是text、ntext或image 数据类型。

下面的语句创建了一个名为@ErrorSave的变量，该变量的数据类型为int，变量的值设置为NULL。

DECLARE @ErrorSave int;

若要声明多个局部变量，可在定义的第一个变量后使用一个逗号，然后指定下一个变量名称和数据类型。例如，此DECLARE 语句创建了三个名为@ProductName、@QuantityPerUnit e 和@ProductID的变量，并将每个变量都初始化为NULL：DECLARE @ProductName nvarchar(30), @QuantityPerUnit nvarchar(20),

@ProductID int;

变量的作用域就是可以引用该变量的SQL 语句的范围。变量的作用域从声明变量的地方开始到声明变量的批处理或存储过程的结尾。

2. 变量赋值。可使用SET 语句为变量赋值，例如，下面的语句将值0赋给变量@ErrorSave。其中，“=”为赋值运算符。

SET @ErrorSave = 0;

3.输出变量的值。可以使用SELECT语句变量的值。例如以下示例首先声明变量@ReturnStatus，然后执行存储过程，将存储过程ProductsByCategory1的返回值赋给@ReturnStatus变量，最后输出变量@ReturnStatus的值：

DECLARE @ReturnStatus int;

EXECUTE @ReturnStatus =ProductsByCategory1 1;

SELECT @ReturnStatus;

也可以使用SELECT语句输出表达式的值。例如：

SELECT'Return code = ' + CAST(@ReturnStatus AS CHAR(10));

如果使用SELECT输出多个变量或表达式的值，则变量或表达式之间用逗号分隔。例如：

SELECT @ProductName, @QuantityPerUnit, @ProductID;

SELECT 表达式1, 表达式2, 表达式2;

4. 使用@@ERROR。@@ERROR是系统函数，它用来检测它的上一条SQL 语句是否执行成功，如果上一条SQL 语句执行成功，则@@ERROR 系统函数返回0；如果上一条SQL 语句语句生成错误，@@ERROR 将返回错误号。

@@ERROR 通常用于检测存储过程是成功还是失败。整数变量（如@ErrorSave）被初始化为0。在每个SQL 语句完成之后，立即测试@@ERROR 是否为0，如果不是0，将@@ERROR 保存到整数变量中。然后，存储过程将在RETURN 语句中返回变量。如果过程中的SQL 语句都没有错误，变量将保持为0。如果一个或多个语句生成错误，变量将包含最后一个错误号。

5. IF 语句。如果IF 语句中条件表达式的值为TRUE ，则执行IF 语句后的语句或语句块。如果IF 语句中条件表达式的值为FALSE ，则跳过IF 语句后的语句或语句块。

例如，在以上存储过程中，如果@@ERROR的值为0，则条件表达式@@ERROR <> 0的值为FALSE，这时不执行SET @ErrorSave = @@ERROR语句。如果@@ERROR的值不为0，则条件表达式@@ERROR <> 0的值TRUE，这时执行SET @ErrorSave = @@ERROR 语句。该语句将@@ERROR的值赋给变量@ErrorSave。

6. 使用BEGIN...END。BEGIN 和END用于定义语句块，它们可以用于定义一系列一起执行的SQL 语句。BEGIN 和END 语句必须成对使用，任何一个均不能单独使用。BEGIN 语句单独出现在一行中，后跟SQL 语句块。最后，END 语句单独出现在一行中，

指示语句块的结束。

BEGIN 和END 语句块必须至少包含一条SQL 语句。

7. RETURN语句。

RETURN 语句无条件从查询、存储过程或批处理中退出。RETURN 语句后面的语句都不执行。RETURN 语句的一般格式为：

RETURN integer_expression

其中integer_expression 为返回的整数值或整型表达式。

当在存储过程中使用RETURN 语句时，此语句可以指定返回给调用程序、批处理或过程的整数值。如果RETURN 未指定值，则存储过程返回0。

以上ProductsByCategory1存储过程中，当执行RETURN @ErrorSave语句时，退出存储过程，并将@ErrorSave作为存储过程的值返回。

8. SQL 语句中可包含存储过程返回的值，但必须以下列格式输入：

EXECUTE @return_status = 。

例如：

EXECUTE @ReturnStatus =ProductsByCategory1 1;

该语句执行存储过程，并将存储过程的返回值赋给变量@ReturnStatus。