执行存储过程

一般用户请忽略本节内容。

假定我们在SQL Server创建了一个存储过程：

CREATE PROCEDURE GetOrders
    @客户 nvarchar(8)
AS
BEGIN
    SELECT * FROM 订单 WHERE 客户=@客户
END

执行此存储过程有两种方法。

方法一

可以直接用存储过程名执行，此时需要将StoredProcedure属性设置为True：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "GetOrders" '指定存储过程名
cmd.StoredProcedure = True '表示CommandText内容不是标准的SQL语句,而是存储过程名
cmd.Parameters.Add("@客户","联想")
Dim dt As DataTable = cmd.ExecuteReader

方法二

也可以用EXEC语句执行存储过程：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "EXEC GetOrders ?"
cmd.Parameters.Add("@客户","联想")
Dim dt As DataTable = cmd.ExecuteReader

下面的语句：

EXEC GetOrders ?

表示执行存储过程GetOrders，这个存储过程需要一个参数（SQL语用"?"表示参数，多个参数用逗号分隔）。

存储过程的三种参数

存储过程有两种参数，分别为输入参数和输出参数。
有的存储过程执行完毕后会返回一个值，这个值需要通过参数获取，我们称之为返回参数。

为区分这是这三种参数，首先需要了解一下SQLCommand的Parameters属性的Add方法的完整语法：

Add(Name, Value)
Add(Name, Value, Output)
Add(Name, Value, Output, Size)

增加输入参数最为简单，例如：

cmd.Parameters.Add("@客户","联想")

增加输出参数需要将Add方法的第三个参数设置为True，例如：

cmd.Parameters.Add("@日期",Date.today,True)

增加返回参数需要将Add方法的第三个参数设置为False，例如：

cmd.Parameters.Add("@Count", 0, False)

接下来我们开始介绍如何使用输出参数和返回参数。

获取输出参数的值

假定我们创建了一个存储过程，用于获取指定编号订单的日期：

CREATE PROCEDURE GetOrderDate
    @订单编号 nvarchar(8),
    @日期 datetime OUTPUT
AS
BEGIN
    SELECT @日期=日期 FROM 订单 WHERE 订单编号=@订单编号
END

这个存储过程有两个参数，第一个为输入参数，用于指定订单编号，第二个为输出参数，用于获取该编号订单的日期。

在Foxtable使用上述存储过程的代码为：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "GetOrderDate"
cmd.StoredProcedure = True
cmd.Parameters.Add("@订单编号","1002")
cmd.Parameters.Add("@日期",Date.today,True)
cmd.ExecuteNonQuery
Dim Val As Date  = cmd.Parameters("@日期") '获取存储过程返回的日期值

注意增加输出参数必须将Add方法的第三个参数设置为True，例如：

cmd.Parameters.Add("@日期",Date.today,True)

如果采用SQL语句执行，需要在语句中将有关参数标记为output，例如：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "exec GetOrderDate ?,? output" '注意第二个参数被标记为output了。
cmd.Parameters.Add("@订单编号","1002")
cmd.Parameters.Add("@日期",Date.today,True)
cmd.ExecuteNonQuery
Dim Val As Date  = cmd.Parameters("@日期")

获取存储过程的返回值

假定我们创建了一个存储过程，用于获取指定日期某客户的全部订单，并返回订单数：

CREATE PROCEDURE GetOrders3 (@CID nvarchar(10), @DT datetime)
AS
SELECT * FROM {订单} Where 客户 = @CID AND 日期=@DT
RETURN @@RowCount

在Foxtable执行以上存储过程的参考代码：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "GetOrders3"
cmd.StoredProcedure = True
cmd.Parameters.Add("@Count",0,False) '这是第一个参数,用于获取存储过程的返回值
cmd.Parameters.Add("@客户","Dell") '第二个参数指定客户
cmd.Parameters.Add("@日期",#4/13/2018#) '第三个参数指定日期
Dim dt As DataTable = cmd.ExecuteReader '获取订单生成DataTable
Dim count As Integer = Cmd.Parameters("@Count") '取得存储过程的返回值

注意增加返回参数必须将Add方法的第三个参数设置为False，例如：

cmd.Parameters.Add("@Count",0,False) '这是第一个参数,用于获取存储过程的返回值

如果采用SQL语句执行，参考代码为：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "exec ?=GetOrders3 ?,?"
cmd.Parameters.Add("@Count",0,True) '这是第一个参数,用于获取存储过程的返回值
cmd.Parameters.Add("@客户","Dell") '第二个参数指定客户
cmd.Parameters.Add("@日期",#4/13/2018#) '第三个参数指定日期
Dim dt As DataTable = cmd.ExecuteReader '获取订单生成DataTable
Dim count As Integer  = Cmd.Parameters("@Count") '取得存储过程的返回值

以上代码采用SQL语句执行存储过程：

exec ?=GetOrders3 ?,?

这里有三个参数，等号左边的参数用于获取存储过程的返回值（但此时不是返回参数，而是输出参数，只有直接执行存储过程时才有返回参数一说），等号右边的是两个输入参数，分别用于指定客户和日期。

三种参数混合使用示例

假定我们定义了如下的存储过程 ，用户获取指定编号订单的客户名称：

CREATE PROCEDURE GetOrderByID
   @订单编号 varchar(8),
   @客户 nvarchar(50) OUTPUT
AS
BEGIN
   SELECT @客户=客户 FROM 订单 WHERE 订单编号=@订单编号
   IF @@Error <> 0
     RETURN -1
   ELSE
     RETURN 100
END

在Foxtable执行上述存储过程的参考代码：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "GetOrderByID"
cmd.StoredProcedure = True
cmd.Parameters.Add("@Err", 12, False) '返回参数
cmd.Parameters.Add("@订单编号","1002") '输入参数
cmd.Parameters.Add("@客户", "", True) '输出参数
cmd.ExecuteNonQuery
Output.Show( cmd.Parameters("@客户"))
Output.Show( cmd.Parameters("@Err"))

需要特别注意的是：返回参数必须作为第一个参数添加，否则会出错。

采用SQL语句执行上述存储过程的参考代码：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Exec ?=GetOrderByID ?,? output"
cmd.Parameters.Add("@Err", 12, True) '用于获取存储过程的返回值,注意这是输出参数,不是输入参数
cmd.Parameters.Add("@订单编号","1002") '输入参数
cmd.Parameters.Add("@客户", "", True) '输出参数
cmd.ExecuteNonQuery
Output.Show( cmd.Parameters("@客户"))
Output.Show( cmd.Parameters("@Err"))