Best Way To Open a Stored Proc

[bobbarker]

Have come across 2 methods for opening a stored procedure as a recordset.

Is either of these (or indeed any other) better than the other:

METHOD 1:

Set rstRecords = New ADODB.Recordset
strSP = "up_parmsel_something (" & lngID & ")"
rstRecords.Open strSP, cnn1, adOpenStatic, adLockReadOnly, adCmdStoredProc

METHOD 2:

With rstRecords
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With 'rstRecords

With objCmd
Set .ActiveConnection = cnn1
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ID", adInteger, adParamInput, , lngID)
.CommandText = "up_parmsel_something"
Set rstRecords = .Execute
End With 'objCmd


Appreciate any comments you may have.

 

[hmckillop]

The second method is probably the "better" way of doing it, as it provides parameter matching, can handle output parameters etc. It takes slightly longer to code but would be my preferred way.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[zemp]

I use a bit of both, but mainly the second method.

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = CONN
   .CommandType = adCmdStoredProc
   .CommandText = "sprName"
   .Parameters.Append .CreateParameter("ID", adInteger, adParamInput, myID)
End With
rs.Open cmd, , adOpenStatic, adLockOptimistic, adCmdStoredProc

zemp

 

[CaptainD]

I use a different method that works for me.

First I have this Function in a module:

Function GetADORSP(strSql As String) As ADODB.Recordset
On Error GoTo Err_GetADORSP

Dim objConn As ADODB.Connection

Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=MySqlServerName;Initial catalog=MyDatabase;User Id=sa;Password=MyPasword;"
Set GetADORSP = New ADODB.Recordset

GetADORSP.Open strSql, objConn, adOpenStatic, adLockReadOnly
Exit Function
Err_GetADORSP:
MsgBox "Error getting data, error #" & Err.Number & ", " & Err.Description

End Function


Then me Sql String in VB is as fallows:

Private Sub MyButton_Click()
Error handler Here
Dim StrSql as string
Dim rs as ADODB.Recordset
StrSql = "Exec spMyStoredProsedure @Myparam1 = MyValue1, @MyParam2 = MyValue2"

Set rs = GetADORSP(StrSql)

Do something with the data
Set rs = nothing
End Sub


I also have a function to Executing Stored Procecdures that do not need to return data and on for an Open Recordset