Come valori di ritorno da una Stored Procedure di SQL Server, ed utilizzarli in Access VBA

Ho creato una Stored Procedure in SQL Server che funziona bene. Ora posso chiamare da VBA, ma desidera restituire un valore per sapere se ci fossero errori etc. L’ultimo parametro, SP è impostato come OUTPUT:

@DataSetID int = 0,
@Destination char(1)='-',
@errStatusOK bit OUTPUT

Mia VBA per chiamare il SP è sotto, ma non ora, dopo aver aggiunto il nuovo parametro e non so dove sto andando male, ho sempre 3708 - Parameter object is improperly defined. Inconsistent or incomplete information was provided.:

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

Come posso ottenere il vba per lavorare con il parametro di OUTPUT e fare il valore di ritorno ‘leggibile’ da vba.

EDIT – ho cambiato la domanda per essere più specificamente sulla restituzione di valori e non solo sull’utilizzo di Parametri di OUTPUT.



4 Replies
  1. 7
    Set cnn = New adodb.Connection
    cnn.ConnectionString = 
       "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"
    
    cnn.Open cnn.ConnectionString
    
    Set cmd = New adodb.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "stprMoveDataSet"
    
    Set param1 = cmd.CreateParameter
                   ("@DataSetID", adInteger, adParamInput, , stDataSet)
    cmd.Parameters.Append param
    Set param2 = cmd.CreateParameter
                   ("@Destination", adChar, adParamInput, 1, stDestination)
    cmd.Parameters.Append param
    Set param3 = cmd.CreateParameter
                   ("@errStatusOK", adBit, adParamOutput, , adParamReturnValue)
    cmd.Parameters.Append param
    
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    rs.Open cmd
    • Si deve avere postato questo mentre stavo postando la mia risposta, ho capito quanto ho sbagliato con CreateParameter ora. Molte grazie, potrebbe utilizzare in futuro.
  2. 6

    Diversi modi che è possibile ottenere valori di utilizzo di VBA.

    1. Recordset
    2. Numero di record interessati (solo per Insert/Update/Delete altrimenti -1)
    3. Parametro di Output
    4. Valore di ritorno

    Mio codice viene illustrato tutti e quattro. Ecco una stored procedure che restituisce un valore:

    Create PROCEDURE CheckExpedite
        @InputX  varchar(10),
        @InputY int,
        @HasExpedite int out
    AS
    BEGIN
        Select @HasExpedite = 9 from <Table>
        where Column2 = @InputX and Column3 = @InputY
    
        If @HasExpedite = 9
            Return 2
        Else
            Return 3
    End

    Qui è il sub che uso in VBA di Excel. Avrai bisogno di un riferimento a Microsoft ActiveX Data Objects 2.8 Library.

    Sub CheckValue()
    
        Dim InputX As String: InputX = "6000"
        Dim InputY As Integer: InputY = 2014
    
        'open connnection
        Dim ACon As New Connection
        'ACon.Open ("Provider=SQLOLEDB;Data Source=<SqlServer>;" & _
        '    "Initial Catalog=<Table>;Integrated Security=SSPI")
    
        'set command
        Dim ACmd As New Command
        Set ACmd.ActiveConnection = ACon
        ACmd.CommandText = "CheckExpedite"
        ACmd.CommandType = adCmdStoredProc
    
        'Return value must be first parameter else you'll get error from too many parameters
        'Procedure or function "Name" has too many arguments specified.
        ACmd.Parameters.Append ACmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
        ACmd.Parameters.Append ACmd.CreateParameter("InputX", adVarChar, adParamInput, 10, InputX)
        ACmd.Parameters.Append ACmd.CreateParameter("InputY", adInteger, adParamInput, 6, InputY)
        ACmd.Parameters.Append ACmd.CreateParameter("HasExpedite", adInteger, adParamOutput)
    
        Dim RS As Recordset
        Dim RecordsAffected As Long
    
        'execute query that returns value
        Call ACmd.Execute(RecordsAffected:=RecordsAffected, Options:=adExecuteNoRecords)
    
        'execute query that returns recordset
        'Set RS = ACmd.Execute(RecordsAffected:=RecordsAffected)
    
        'get records affected, return value and output parameter
        Debug.Print "Records affected: " & RecordsAffected
        Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")
        Debug.Print "Output param: " & ACmd.Parameters("HasExpedite")
    
        'use record set here
        '...
    
        'close
        If Not RS Is Nothing Then RS.Close
        ACon.Close
    
    End Sub
  3. 4

    Avevo inizialmente guardato i Parametri di OUTPUT, ma non potevano scoprire come ottenere di nuovo l’Accesso (in VBA) per poi fornire un feedback all’utente. Un collega ha suggerito di utilizzare una SELECT nella Stored procedure e di utilizzare questo.

    STORED PROCEDURE:
    Aggiunto il seguente fine:

    SELECT @errStatusOK as errStatusOK, @countCurrent as countCurrent, @countHistorical as countHistorical

    VBA:

    Dim cnn As ADODB.Connection
    Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param As New ADODB.Parameter
    Dim fld As ADODB.Field
    Dim stMessage As String
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"
    
    cnn.Open cnn.ConnectionString
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "stprMoveDataSet"
    
    Set param = cmd.CreateParameter("@DataSetID", adInteger, adParamInput, , stDataSet)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@Destination", adChar, adParamInput, 1, stDestination)
    cmd.Parameters.Append param
    
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    'rs.Open cmd
    Set rs = cmd.Execute
    If rs!errstatusok = True Then
        stMessage = "Operation appears to have been successful, check the DataSets Listing..." & Chr(13) & "Also, the Server returned the following information: ["
    Else
        stMessage = "Operation appears to have failed, check the DataSets Listing..." & Chr(13) & "Also, the Server returned the following information: ["
    End If
    For Each fld In rs.Fields
        stMessage = stMessage & "| " & fld.Name & " /" & fld.Value & " |"
    Next fld
    stMessage = stMessage & "]"
    
    MsgBox stMessage

    Restituisce il folliwing:
    Operation appears to have failed, check the DataSets Listing...
    Also, the Server returned the following information: [| errStatusOK /False || countCurrent /0 || countHistorical /10 |]

    • Si può certamente utilizzare i parametri di output come valore di ritorno o di record interessati. Vedi la mia risposta.
  4. 2

    Tra l’altro parametro enumerazioni da cui “adParamInput” è preso, l’altro è “adParamOutput”, che è quello di indicare un parametro da una stored procedure, e “adParamInputOutput” per un parametro che va “in entrambe le direzioni,” per così dire. Nel tuo caso, credo che “adParamOutput” sarebbe opportuno. Spero che questo è quello che stai cercando.

    • Non è quello che stavo cercando, ma utile. Un Collega mi ha suggerito di fornire in uscita tramite una SELECT @errStatusOK ecc e manipolare in VBA, che ha funzionato a meraviglia. Grazie

Lascia un commento