Call SQL Stored Procedure With 2 Parameters Using VBA

Use the below code to Call an SQL Stored Procedure With 2 Parameters.

All you have to change is the Parameter names & values, the ConnectionString, the StoredProcName & you should be good to go.

Also set the reference to the latest available Microsoft ActiveX Data Objects Library by going to Tools > References

---------------------------------------------------

Sub CallStoredProcedure() Dim Conn As ADODB.Connection, RecordSet As ADODB.RecordSet

Dim Command As ADODB.Command Dim ConnectionString As String, StoredProcName As String Dim LoginID As ADODB.Parameter, Entity_Id As ADODB.Parameter Application.ScreenUpdating = False Set Conn = New ADODB.Connection Set RecordSet = New ADODB.RecordSet Set Command = New ADODB.Command ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.2.200\SalesDB;INITIAL CATALOG=DB_Common; User Id=MyUserName; Password=password123;" On Error GoTo CloseConnection Conn.Open ConnectionString StoredProcName = "Stored_Procedure_Name" With Command .ActiveConnection = Conn .CommandType = adCmdStoredProc .CommandText = StoredProcName End With Set LoginID = Command.CreateParameter("@LoginID", adVarChar, adParamInput, 100, "MB51")

Command.Parameters.Append LoginID Set Entity_Id = Command.CreateParameter("@Entity_Id", adInteger, adParamInput, , "23") Command.Parameters.Append Entity_Id Set RecordSet = Command.Execute Sheets("Sheet1").Range("A1").CopyFromRecordset RecordSet RecordSet.Close Conn.Close On Error GoTo 0 Application.ScreenUpdating = True Exit Sub CloseConnection: Application.ScreenUpdating = True MsgBox "SQL Stored Procedure Did Not Execute Sucessfully!", vbCritical, "SQL Error" Conn.Close End Sub

---------------------------------------------------

#excelprogramming #ADODB #SQL #StroedProcedure #VBA

Follow Us
  • Twitter Basic Square
  • Google+ Basic Square
Recent Posts
Search By Tags
No tags yet.