Call SQL Server Stored Procedure

[valdosta]

Hi, can I call a SQL Server 2000 stored procedure from vb6 using DAO? Thanks in Advance!

 

[ManishV]

This is an example of executing a SP using DAO.

Cheers
Manish


Sub DirectionX()

Dim wrkMain As Workspace
Dim conMain As Connection
Dim qdfTemp As QueryDef
Dim rstTemp As Recordset
Dim strSQL As String
Dim intLoop As Integer

' Create ODBC workspace and open a connection to a
' Microsoft SQL Server database.
Set wrkMain = CreateWorkspace("ODBCWorkspace", _
"admin", "", dbUseODBC)
Set conMain = wrkMain.OpenConnection("Publishers", _
dbDriverNoPrompt, False, _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers&quot

' Set SQL string to call the stored procedure
' getempsperjob.
strSQL = "{ call getempsperjob (?, ?) }"

Set qdfTemp = conMain.CreateQueryDef("", strSQL)

With qdfTemp
' Indicate that the two query parameters will only
' pass information to the stored procedure.
.Parameters(0).Direction = dbParamInput
.Parameters(1).Direction = dbParamInput

' Assign initial parameter values.
.Parameters(0) = "0877"
.Parameters(1) = 0

Set rstTemp = .OpenRecordset()

With rstTemp
' Loop through all valid values for the second
' parameter. For each value, requery the recordset
' to obtain the correct results and then print out
' the contents of the recordset.
For intLoop = 1 To 14
qdfTemp.Parameters(1) = intLoop
.Requery
Debug.Print "Publisher = " & _
qdfTemp.Parameters(0) & _
", job = " & intLoop
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop
Next intLoop
.Close
End With

End With

conMain.Close
wrkMain.Close

End Sub