Calling SQL Server Store Procedure with parameters

  • Thread starter Thread starter BobD
  • Start date Start date
B

BobD

I have a Access Query that will be calling a SQL Server store procedure
that has two parameters. I have the SQL (exec p_get_sla @sd, @ed ;) to
call the store procedure as passthru but I'm not sure how the
parameters are to be assigned dynamically. Is this possible?
 
You have to create a pass-through query, and dynamically generate the SQL
for it.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "exec p_get_sla " & param1 & ", " & param2

Set qdfCurr = CurrentDb.CreateQueryDef("MyQuery")
qdfCurr.Connect = <appropriate connect string>
qdfCurr.SQL = strSQL
 
Douglas said:
You have to create a pass-through query, and dynamically generate the SQL
for it.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "exec p_get_sla " & param1 & ", " & param2

Set qdfCurr = CurrentDb.CreateQueryDef("MyQuery")
qdfCurr.Connect = <appropriate connect string>
qdfCurr.SQL = strSQL
Click to expand...

If the parameter type is text, they need to be enclosed in single
quotes in the SQL string; if the parameter value contains single
quotes, they need to be escaped in the in the SQL string. This is just
one potential issue which can be avoided by using explicit parameter
objects in the middleware. Does DAO have them? For SQL Server, perhaps
ADO is best anyhow:

Sub testparam2()
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open <appropriate connect string>

Dim param1 As ADODB.Parameter
Set param1 = New ADODB.Parameter
With param1
.Name = "@sd"
.Direction = adParamInput
.Type = adVarChar
.Size = 35
.Value = "O'Reilly"
End With

Dim param2 As ADODB.Parameter
Set param2 = New ADODB.Parameter
With param2
.Name = "@ed"
.Direction = adParamInput
.Type = adVarChar
.Size = 35
.Value = "Moe's Bar"
End With

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

' Create new Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "p_get_sla"

.Parameters.Append param1
.Parameters.Append param2

End With

' Create new rs (assuming proc returns a resultset)
Set rs = New ADODB.Recordset
With rs
Set .Source = cmd
.Open
If Not .EOF Then
' Do something with rs
Debug.Print .Fields(0).Value
End If
.Close
End With

con.Close
End Sub
 
If the parameter type is text, they need to be enclosed in single
quotes in the SQL string; if the parameter value contains single
quotes, they need to be escaped in the in the SQL string. This is just
one potential issue which can be avoided by using explicit parameter
objects in the middleware. Does DAO have them? For SQL Server, perhaps
ADO is best anyhow:
Click to expand...

Yes, DAO has parameters, but you can't use them with pass-through queries.
You're correct that ADO might be better in this situation.
 
Back
Top