执行存储过程

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

假定我们在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)

Name 参数名称
Value 参数的值
Output 逻辑型,省略表示输入参数,True表示输出参数,False表示返回参数。
Size 整数型,用于指定输入参数或返回参数的大小,默认为32,只有字符型的参数才需要设定。

增加输入参数最为简单,例如:

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"))


本页地址:http://www.foxtable.com/webhelp/topics/3267.htm