Excel VBA: Execute SQL Server Stored Procedure for Each Row Based on Cell Values as Parameters

I am in the process of creating an excel macro that calls a SQL Server stored procedure and populates a column in a spreadsheet.

There are three columns in the spreadsheet: "Student Name", "Course Title" and "Enrollment Status". I want to be able to use "Student Name"  and "Course Title" cell values as parameters for the stored procedure.

The stored procedure is going to evaluate every row in the spreadsheet for that "Student Name" and "Course Title" (row combination) and populate the enrollment status in the "Enrollment Status" column.

This is a simple Stored Procedure that is querying a small data set so I am not worried about performance.

Note: This is conceptually identical to what I am trying to accomplish however I do not want to display business specific subject matter.

Example: A1 = Janet Smith, B1 = Physics. A1 and B1 are parameters for the stored procedure which then returns C1 that indicates enrollment status. I.E. "Janet Smith", "Physics", "Not
                          Registered"
                 A2 = Samuel Adams, B2 = Biology. A2 and B2 are parameters for the stored procedure which then returns C2 that indicates enrollment status. I.E. "Samuel Adams", "Biology",
                        "Registered"

This is what I came up with to take a single cell as a parameter. My questions is how can I do this for a range of cells (A1:A100, B1:100, C1:100).

Thanks in advance for your taking the time to look at this. Much appreciated.

Private Sub CommandButton1_Click()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

Set cn = New ADODB.Connection

strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=SQLABCD;INITIAL CATALOG=DBXYZ;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "usp_SampleProc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

'only have single set of parameters so far. Need to execute stored procedure for all rows
cmd.Parameters(1).Value = Range("A1")
cmd.Parameters(1).Value = Range("B1")

Set rs = cmd.Execute()

If Not rs.EOF Then
Worksheets("Enrollment").Range("C1:C100").CopyFromRecordset rs
rs.Close

Else
MsgBox "No data."
End If

End Sub
Sample_Spreadsheet.xlsx
Rob M.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
So you want to execute the stored procedure for each row of data, taking the parameters from column A and B and putting the result in column C?

Perhaps something like this then.
Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Dim I As Long

    Set cn = New ADODB.Connection

    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "SERVER=SQLABCD;INITIAL CATALOG=DBXYZ;"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    cn.Open strConn

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandText = "usp_SampleProc"
    cmd.CommandType = adCmdStoredProc

    With Sheets("Sheet1")

        For I = 2 To .Range("A" & Rows.Count).End(xlUp).Row

            'only have single set of parameters so far. Need to execute stored procedure for all rows
            cmd.Parameters(1).Value = Range("A" & I).Value
            cmd.Parameters(1).Value = Range("B" & I).Value
            cmd.Parameters.Refresh

            Set rs = cmd.Execute()

            If Not rs.EOF Then
                Worksheets("Enrollment").Range("C1" & I).Value = rs.Fields(0)
                rs.Close
            Else
                MsgBox "No data."
            End If
        Next I

    End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob M.Author Commented:
Norie. Thanks very much! This was very helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.