3

I am attempting to get an output variable (the new identity column) from a stored procedure in SQL Server 2008 after executing the procedure from Access 2013 VBA. I don't fully understand all the adodb stuff, and I've tried a number of things from different articles to no avail. Here's where I am with it now...

Stored procedure:

PROCEDURE [proc_NewClient]
    @Type INT, 
    @PhoneIntakeID INT,
    @ClientID INT = NULL,
    @NewPSID INT = null OUTPUT (2 possible ways to call the proc, one does not produce an output variable)

   INSERT INTO tblClientDetails (ClientID, PhoneIntakeID, Client_Status) 
   VALUES (@ClientID, @PhoneIntakeID, 'Applicant')

   DECLARE @NewClientDetailID int
   SELECT @NewClientDetailID = SCOPE_IDENTITY()

   INSERT INTO tblPS_Psychosocial (ClientDetailID, Client) 
   VALUES (@NewClientDetailID, @ClientID)

   SELECT @NewPSID = SCOPE_IDENTITY()

   INSERT INTO tblPS_AbuseHistory (PsychosocialID) 
   VALUES (@NewPSID)

   INSERT INTO tblPS_FamilyHistory(PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_FinancialHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_LegalHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_MedicalHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_PsychiatricHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_SocialHistory (PsychosocialID)  VALUES (@NewPSID)
   INSERT INTO tblPS_SpiritualHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_SubstanceHistory (PsychosocialID) VALUES (@NewPSID)

   INSERT INTO tblVocAssessment(ClientDetailID) VALUES (@NewClientDetailID)

And from the form I've got:

    Dim cnn As ADODB.Connection
            Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param1 As New ADODB.Parameter, param2 As New ADODB.Parameter, param3 As New ADODB.Parameter, param4 As New ADODB.Parameter
            Set cnn = New ADODB.Connection
            cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SRV-DB01;DATABASE=TWHClientMgmt;Trusted_Connection=Yes"

            cnn.Open cnn.ConnectionString

            Set cmd = New ADODB.Command
            cmd.ActiveConnection = cnn
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "[THEWOMENSHOME\jboyd].proc_NewClient"


            Set param1 = cmd.CreateParameter("@Type", adinteger, adparamInput, , 2)
            cmd.Parameters.Append param1
            Set param2 = cmd.CreateParameter("@PhoneIntakeID", adinteger, adparamInput, , Me.PhoneIntakeID)
            cmd.Parameters.Append param2
            Set param3 = cmd.CreateParameter("@ClientID", adinteger, adparamInput, , intNewID)
            cmd.Parameters.Append param3
            Set param4 = cmd.CreateParameter("@NewPSID", adinteger, adParamOutput)
            cmd.Parameters.Append param4

rs.open cmd

To this point the code works, the new records are generated, etc. When I run the stored procedure from SQL Server it returns the correct identity column number, but I've tried multiple ways to reference the output in VBA and always end up coming up with a null. How do I reference it here correctly?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NPO_Coder
  • 87
  • 1
  • 3
  • 8
  • 3
    Instead of `rs.Open` try `cmd.Execute` and then see if you can retrieve the value of the output parameter with `cmd.Parameters("@NewPSID").Value`. – Gord Thompson Dec 08 '15 at 01:51
  • 1
    You can also make SP returning recordset with one row/one column with new ID data and read this data using select clause in pass-through query – Sergey S. Dec 08 '15 at 02:47
  • @GordThompson thank you! I had tried that combination but WITHOUT the .value on the parameter, that was the ticket!! It works perfectly now! – NPO_Coder Dec 09 '15 at 14:00
  • @SergeyS. How would I script a passthrough query like that, that both executes the insert and returns the value? Would I just execute it with ReturnsRecords set to true, all the parameters supplied, and end the stored procedure with a select? How would you reference the value in vba? It seems like if that works it would be a much simpler solution than what I've been doing. – NPO_Coder Dec 09 '15 at 14:08
  • 1
    The trade-off in using a stored procedure that returns a result set and invoking it with a pass-through query is that pass-through queries cannot be *parameterized*, so you have to use *dynamic SQL* to "glue together" the EXEC statement each time. Therefore you need to escape ("sanitize") all of the inputs, quote them properly (including `''` or `N''`, depending on the type of string), and so on. – Gord Thompson Dec 09 '15 at 14:26
  • 1
    Yes, just use procedure with select at the end, in VBA code use regular OpenRecordset for querydef. Set the qurydef `sql` propery to `select or pass-through query name first. And yes, you cannot parametryze such kind queries. – Sergey S. Dec 09 '15 at 16:30

2 Answers2

11

To retrieve the value of a stored procedure's OUTPUT parameter you simply .Execute the ADODB.Command and then retrieve the .Value of the corresponding ADODB.Parameter, like so:

cmd.Execute
' retrieve and display the returned OUTPUT parameter value
Debug.Print cmd.Parameters("@NewPSID").Value
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

Simple DAO solution calling stored procedure:

Public Function RunSqlQuery(q As String) As Variant

Dim cdb As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As Recordset

Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_Setup").Connect  ' your attached table
qdf.SQL = q
qdf.ReturnsRecords = True
Set rs = qdf.OpenRecordset()
RunSqlQuery = rs.Fields(0)  ' you also can output more than one parameter from a stored procedure as fields
rs.Close
Set rs = Nothing
Set qdf = Nothing
Set cdb = Nothing

End Function

Now, call the function, define a query the same way you would do at the SQL server:

? RunSQLQuery("DECLARE @param1 varchar(16);   exec proc_MyStoredProcedure @param1 OUTPUT;  SELECT @param1")
Zoran Hrvoic
  • 1
  • 2