Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calling SQL Server Store Procedure with parameters

Discussion in 'Information Technology' started by BobD, Nov 18, 2005.

  1. BobD

    BobD
    Expand Collapse
    Guest

    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?
     
  2. Loading...

    Similar Threads Forum Date
    Your Calling Get Involved Jun 5, 2016
    How Harnarayan Singh Found His Calling Sikh Personalities Apr 13, 2016
    Zen & UnZen Moments...Who Are You Calling Yo-Yo...? Inspirational Stories Jul 14, 2013
    SALDEF Calling All College Students and Recent Graduates! (SALDEF) Sikh Organisations Mar 15, 2011
    General What is Your Calling: Expanding the Conversation about Why We Are Here Hard Talk Jan 3, 2011

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "BobD" <bdieckman@sbcglobal.net> wrote in message
    news:1132265964.970156.140270@g49g2000cwa.googlegroups.com...
    >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?
    >
     
  4. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Douglas J. Steele wrote:
    > 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


    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
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    <peregenem@jetemail.net> wrote in message
    news:1132311466.312923.159790@o13g2000cwo.googlegroups.com...
    >
    >
    > 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:


    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.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)
     

Share This Page