吐血推荐---excel VBA调用SQLserver中带参数的存储过程

zouhy0442

1，不带参数的，直接在连接或Recordset 中用“EXEC 过程名”调用即可。
Private Sub cmdPrepareNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
   
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                   "AS " & _
                   "Update Employees " & _
                   "SET HourlySalary = 12.50;"
   
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub
调用程序如下：
Private Sub cmdNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
   
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "EXECUTE SetNewMinSalary;"
   
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

2，带一个参数的，也可以直接调用，如下例子
Private Sub cmdCreateProcedure_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
   
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                   "(NewMinSalary Currency) " & _
                   "AS " & _
                   "Update Employees " & _
                   "SET HourlySalary = NewMinSalary " & _
                   "WHERE HourlySalary < NewMinSalary;"
                  
    conEmployees.Execute strProcedure
   
    MsgBox "The new stored procedure has been created."
    conEmployees.Close
End Sub

调用时直接输入一个参数。
Private Sub cmdExecuteProcedure_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
   
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "EXECUTE SetNewMinSalary 14.50;"
   
    conEmployees.Execute strProcedure
   
    MsgBox "The minimum hourly salary has been set to 14.50/hr"
    conEmployees.Close
End Sub

3：最麻烦的是带有多参数的，如下面的例子带有两个参数。
直接从SQL server复制过程如下：
CREATE PROCEDURE [dbo].[Max_2ExcelDB]
(
        @Territory varchar(10)='All',
        @Country varchar(10)='CN'
)
AS
        set nocount on
        if @Territory='All'
                BEGIN
                        select * from CustomerDB
                        set nocount off
                END
        else
                begin
                        select a.* from CustomerDB a left join DistributorDB b
                        on a.Distributor=b.Distributor
                        where b.Territory in (@Territory,'Open') and a.Country=@Country
                end
        set nocount off
调用程序如下，这时候就要请出ADODB.Command对象了：
Sub CallmyProc()
    Dim conn As ADODB.Connection
    Dim MyProc As ADODB.Command
   
    Dim sSql As String
    Dim SrcArr

    Set conn = New ADODB.Connection
    Set MyProc = New ADODB.Command
    conn.ConnectionString = connStr(1)
    conn.Open
   
    If conn.State = adStateOpen Then
        With MyProc
            .ActiveConnection = conn
            .Prepared = True
            .CommandText = "Max_2ExcelDB"
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter("@Territory", adChar, adParamInput, 10, "South")
            .Parameters.Append .CreateParameter("@Country", adChar, adParamInput, 10, "CN")
            ThisWorkbook.Sheets("sheet4").Cells(1, 1).CopyFromRecordset .Execute
        End With
    Else
        MsgBox " 无法打开数据库"
    End If

    conn.Close
    Set conn = Nothing
    Set MyProc = Nothing
End Sub

4，更麻烦的是带有多参数，还有返回参数的，也是用Adodb.command对象解决。下面代码来自网络http://www.officefans.net/cdb/viewthread.php?tid=91224：
Set 连接 = CreateObject("ADODB.Connection")
Set 过程 = CreateObject("ADODB.Command")
连接.Open "Driver={SQL Server};Server=192.168.1.2;Database=数据库名_A;uid=共用登录;pwd=123456"
起始日期="2007-12-1"
截止日期="2008-3-1"
With 过程
.ActiveConnection = 连接
.Prepared = True
.CommandText = "vba_分数统计"
.CommandType = 4
.Parameters.Append .CreateParameter("@起始日期", 200, 1, 20, 起始日期)  ‘append 与后面的点. 需要加空格，即将createparameter返回值传给append方法。
.Parameters.Append .CreateParameter("@截止日期", 200, 1, 20, 截止日期)
.Parameters.Append .CreateParameter("@姓名", 200, 1, 50, Worksheets(2).Cells(2, 1).Value)
.Parameters.Append .CreateParameter("@返回分数", 2, 2)
.Execute
Worksheets(1).Cells(1,1).Value = .Parameters("@返回分数")
.Close
Set 连接 = Nothing
end with

zouhy0442

上面的例子中为参数赋值采取的是追加参数法，即用createparameter及append方法；
还可以更简单一些的直接赋值，如例3中：
            .Parameters(1).Value = "North"
            .Parameters(2).Value = "CN"

xiaohuaxiong

高人，仰望！

970917716

学习！！！！

970917716

后台通过用过程写存储过程时，出现'这种对象不支持该操作的'的错误，求助，该怎么解决

paulc

请问：调用ORACLE存储过程时，如上述几类情况，其语句有何差异呢？谢谢

seoul0752

学习！！！！

jingmeiopto

你好，看了你的VBA连接MS SQL存储过程的文章。
想请你收费帮助  使用VBA连接存储过程，请帮助。
但是因为没有权限，所以无法与你联系
请联系我，Q：2850145987

zz600088

值得好好学习。谢谢。

Jeff2008

mark一下，好好学学