execute stored procedure

  • Thread starter Thread starter mcnewsxp
  • Start date Start date
M

mcnewsxp

could i get an example of how to call a sql stored proc that requires 4
input parms and returns 1?
DAO or ADO or both.
thanks much,
mcnewsxp
 
The easiest way is to use a pass-through query, and change the SQL to
include the parameters.

Let's assume the pass-through query is named qryPassthrough.

Dim qdfPassthrough = DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set qdfPassthrough = CurrentDb().QueryDefs("qryPassthrough")
strSQL = "EXEC MyStoredProc 'Parm1', 23453, '2007-10-28', 'Parm4'"
qdfPassthrough.SQL = strSQL
Set rsCurr = qdfPassthrough.Execute
MsgBox "The stored procedure returned " & rsCurr.Fields(0)
 
no connect string or DSN info needed?
The easiest way is to use a pass-through query, and change the SQL to
include the parameters.

Let's assume the pass-through query is named qryPassthrough.

Dim qdfPassthrough = DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set qdfPassthrough = CurrentDb().QueryDefs("qryPassthrough")
strSQL = "EXEC MyStoredProc 'Parm1', 23453, '2007-10-28', 'Parm4'"
qdfPassthrough.SQL = strSQL
Set rsCurr = qdfPassthrough.Execute
MsgBox "The stored procedure returned " & rsCurr.Fields(0)
Click to expand...
 
mcnewsxp said:
no connect string or DSN info needed?
Click to expand...


While in a sense yes and in a sense no.

The idea here is that you build a simple pass-through query something like:

select * from tblCustomers

If you make a query to pass through, then you'll supply all of that connects
information when you build that query. Thus Douglas is suggesting is to use
an *existing* query that already has all that wonderful connection
information
set up correctly.

Since the pass through query connection information is already set up, then
the only thing left to do is provide the actual SQL.

That SQL you provide can be virtually any command that the SQL server can
receive as a command line prompt (a pass through query simply passes
whatever text you sent to the server un modified -- the SQL or execute
commands). It does NOT actually have to be SQL.
 
The easiest way is to use a pass-through query, and change the SQL to
include the parameters.

Let's assume the pass-through query is named qryPassthrough.

Dim qdfPassthrough = DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set qdfPassthrough = CurrentDb().QueryDefs("qryPassthrough")
strSQL = "EXEC MyStoredProc 'Parm1', 23453, '2007-10-28', 'Parm4'"
qdfPassthrough.SQL = strSQL
Set rsCurr = qdfPassthrough.Execute
MsgBox "The stored procedure returned " & rsCurr.Fields(0)
Click to expand...

i get an "expected function or variable" error on the .Execute.
 
could i get an example of how to call a sql stored proc that requires 4
input parms and returns 1?
DAO or ADO or both.
thanks much,
mcnewsxp
Click to expand...

this is what i was looking for:

Dim objCmd As New ADODB.Command
Dim longConveyance As Long

objCmd.ActiveConnection = "Provider=SQLOLEDB;Server=SQI-CDCDV1.CDC.GOV
\QSRV1;Database=SCANNEDFORMSEPIX;Trusted_Connection=yes;"
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "spInsertConveyance"
objCmd.Parameters.Refresh
objCmd.Parameters(1).Value = txtAirlineCode
objCmd.Parameters(2).Value = txtArrivalAirportCode
objCmd.Parameters(3).Value = txtFlightNumber
objCmd.Parameters(4).Value = txtArrivalDate
objCmd.Parameters(5).Value = txtArrivalState
objCmd.Parameters(6).Value = Null
objCmd.Execute

longConveyance = objCmd.Parameters(0).Value
longConveyance = objCmd.Parameters(6).Value

Set objCmd = Nothing
 
The implication is that you didn't supply all of the needed parameters when
you rewrote the SQL for the pass-through query.
 
The implication is that you didn't supply all of the needed parameters when
you rewrote the SQL for the pass-through query.
Click to expand...

when i changed
Set rsCurr = qdfPassthrough.Execute
to
qdfPassthrough.Execute
it ran.
but no return value.
the ADO version is much simpler and less obfuscated IMO.
 
Back
Top