Invoking Stored Procedure in VBA

I have created a stored procedure sp_displayspl_Members in MSSQL Server.
Code to create Stored procedure:
if exists(select * from sysobjects where name='sp_displayspl_Members')begindrop procedure sp_displayspl_Members;endgocreate procedure sp_displayspl_Members @membertype char(8)as
Select
Through this stored procedure we have selected records where Membertype='Social'

In order to open a stored procedure within ActiveX Data Objects (ADO), you must first open a Connection Object, then a Command Object, fill the Parameters Collection with one parameter in the collection for each parameter in the query, and then use the Command.Execute() method to open the ADO Recordset. VBA Code:


Sub callSP()
Dim con1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Dim reccounter As Long
Dim spcreate, spdrop As String
On Error GoTo Errorhandler
spdrop = "if exists(select * from sysobjects where name='sp_displayspl_Members') drop procedure sp_displayspl_Members"
spcreate = "create procedure sp_displayspl_Members @membertype char(8) as Select * from Member where Membertype=@membertype"
        con1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=NEW\SQLEXPRESS;"
        con1.Open
          
        Set rs1 = con1.Execute(spdrop)
        Set rs1 = Nothing
           
         Set rs1 = con1.Execute(spcreate)
        Set rs1 = Nothing
        
       
        cmd1.ActiveConnection = con1
        cmd1.CommandText = "sp_displayspl_Members"
        cmd1.CommandType = adCmdStoredProc
       
        cmd1.Parameters(1).Value = "Social"
       
        
        Set rs1 = cmd1.Execute()
       
        Do While Not rs1.EOF
        reccounter = reccounter + 1
               val1 = rs1(0)
               val2 = rs1(1)
               val3 = rs1(2)
               val4 = rs1(3)
               val5 = rs1(4)
               val6 = rs1(5)
               val7 = rs1(6)
               val8 = rs1(7)
                Cells(reccounter, 1) = val1
                Cells(reccounter, 2) = val2
                Cells(reccounter, 3) = val3
                Cells(reccounter, 4) = val4
                Cells(reccounter, 5) = val5
                Cells(reccounter, 6) = val6
                Cells(reccounter, 7) = val7
                Cells(reccounter, 8) = val8
                rs1.MoveNext
               
        Loop
        If rs1.State <> adStateClosed Then
                rs1.Close
                con1.Close
                Set rs1 = Nothing
                Set con1 = Nothing
         End If
Exit Sub
Errorhandler:
    MsgBox "Error description:" & Err.Description
End Sub * from Member where Membertype=@membertype