How to retrieve returned values from and MS Access Passthru Query , executing a SQL stored procedure

Try set rs = .OpenRecordset instead of .Execute where rs is a recordset variable

I'm not really clear on what you mean.  I tried this but got "Invalid use of property'

Private Sub EEPassThru_ExecuteSproc_ValueReturned()

' the connection will vary based on test or production
' in test it will vary even by userID

gConnection = "ODBC;Description=JTS Data;DRIVER=SQL Server;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"

Dim Muni As Long
Dim returnNextReceipt As Long
'
Dim db As DAO.Database
Set db = getCurrentDbC
Dim rs As DAO.Recordset
'
Muni = 877
returnNextReceipt = 40
'
' Add an event EE Way 1
'
With DBEngine(0)(0).CreateQueryDef("")
         .Connect = gConnection
         .ReturnsRecords = True
         .SQL = "EXEC dbo.spGetNextReceiptNum"
         .SQL = .SQL & "  @MuniCode         = " & Muni
         .SQL = .SQL & ", @NewReceiptNumber = " & returnNextReceipt    ' this is returned field
         rs = .OpenRecordset
End With
'

End Sub

rs = .OpenRecordset  does nothing.  The code is not even execute the pass through query (SQL).
I suspect he meas   rs.OpenRecordset(.SQL) but I don't think that is going to work here.

A Stored Procedure will normally return a zero if successful.   Google Stored Procedure return value

Dim x
'  code
x = .Execute dbSQLPassThrough + dbFailOnError
Debug.Print x   ' will be zero if successful


Stored procedures can also generate Output (to where I'm not sure)
Google that as well

You need to use a "Set" statement.  Please refer to my prior statement.

DatabaseMX: I have stored procedures return multiple values. The following returns multiple values

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spTAYearSummary]    Script Date: 4/23/2017 7:20:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spTAYearSummary]
      -- Add the parameters for the stored procedure here
      @passedTaxAuthorityID int = 0,
      @passedFromYear       int = 0,
      @passedThruYear       Int = 9999,
      @returnFaceSum        Decimal(10,2) = 0 OUTPUT,
      @returnPenaltySum     Decimal(10,2) = 0 OUTPUT,
      @returnInterestSum    Decimal(10,2) = 0 OUTPUT,
      @returnLienSum        Decimal(10,2) = 0 OUTPUT,
      @returnSvcChgSum      Decimal(10,2) = 0 OUTPUT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT  
         @returnFaceSum = SUM([FaceBal]) ,
         @returnPenaltySum = SUM([PenaltyBal]),
         @returnInterestSum = SUM([InterestBal]),
         @returnLienSum = SUM([LienCostBal]),
         @returnSvcChgSum = SUM([SvcChgBal])
      FROM tblTaxYears
      WHERE TaxAuthorityRecID =  @passedTaxAuthorityID
      --  and TaxYear Between @passedFromYear and @passedThruYear
         --PayStatusID <> 1 and

--- if any values are null, set them to 0         

      if @returnFaceSum is null
            set @returnFaceSum = 0;

      if @returnPenaltySum is null
            set @returnPenaltySum = 0;

      if @returnInterestSum is null
            set @returnInterestSum = 0;

      if @returnLienSum is null
            set @returnLienSum = 0;

      if @returnSvcChgSum is null
            set @returnSvcChgSum = 0;

END


I already have ADO code to execute these (below) but was trying to take your suggestion to code them using a passthru query.  If the passthru method works I would use it on any future call I make to SP's returning one or more values.

'
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim param3 As New ADODB.Parameter

Dim param4 As New ADODB.Parameter
Dim param5 As New ADODB.Parameter
Dim param6 As New ADODB.Parameter
Dim param7 As New ADODB.Parameter
Dim param8 As New ADODB.Parameter

With cmd
    .CommandText = "spTAYearSummary"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    Set param1 = .CreateParameter("passedTaxAuthorityID", adBigInt, adParamInput, , wkTaxAuthorityID)
    .Parameters.Append param1
    '
    Set param2 = .CreateParameter("passedFromYear", adBigInt, adParamInput, , wkFromYear)
    .Parameters.Append param2
    '
    Set param3 = .CreateParameter("passedThruYear", adBigInt, adParamInput, , wkThruYear)
    .Parameters.Append param3
    '
    ' Output Params
    '
    Set param4 = .CreateParameter("returnFaceSum", adDouble, adParamOutput)
    .Parameters.Append param4
    '
    Set param5 = .CreateParameter("returnPenaltySum", adDouble, adParamOutput)
    .Parameters.Append param5
    '
    Set param6 = .CreateParameter("returnInterestSum", adDouble, adParamOutput)
    .Parameters.Append param6
    '
    Set param7 = .CreateParameter("returnLienSum", adDouble, adParamOutput)
    .Parameters.Append param7
    '
    Set param8 = .CreateParameter("returnSvcChgSum", adDouble, adParamOutput)
    .Parameters.Append param8
   
    .Execute Options:=adExecuteNoRecords
    Set .ActiveConnection = Nothing
    Set param1 = Nothing
    Set param2 = Nothing
    Set param3 = Nothing
End With
'
returnFaceBal = cmd.Parameters("returnFaceSum")
returnPenaltyBal = cmd.Parameters("returnPenaltySum")
returnInterestBal = cmd.Parameters("returnInterestSum")
returnLienBal = cmd.Parameters("returnLienSum")
returnSvcChgBal = cmd.Parameters("returnSvcChgSum")

Set param4 = Nothing
Set param5 = Nothing
Set param6 = Nothing
Set param7 = Nothing
Set param8 = Nothing
'
Set cmd = Nothing



aikimark:  Perhaps you can alter the code to be the way you are suggesting.  I really am not seeing what you are suggesting.

Hi,

in "normal" QueryDefs you could use the "PARAMETERS" clause to define typed parameters to be used in a DAO query. Unfortunately that is not possible with a PT query as the whole query text is directly sent to the database server which doesn't know what "PARAMETERS" is or how to fill these variables.
In SQL Server you would use DECLARE statements to define variables which could be filled with the returned values. That is also possible with a PT query, to get the values of the variables back you only need to add a SELECT statement at the end of your PT query which reads out the variables and give them a name.

That would look like this (with an example SP which fills two OUTPUT parameters):

DECLARE @intOutput AS int;
DECLARE @strOutput AS nvarchar(MAX);

EXECUTE dbo.procTestOUTPUT
    @intOutput = @intOutput OUTPUT,
    @strOutput = @strOutput OUTPUT;

SELECT @intOutput AS '@intOutput', @strOutput AS '@strOutput';

Select allOpen in new window

(Of course you do not need to name the columns of the last SELECT like in this example where I named them the same as the variable name, you can name that like you want.)

The PT query must be set to return values and then you can assign that to a DAO recordset and read out the values with the fields collection of the recordset object.

But as this method needs more coding it would be easier to simply use a SELECT statement inside of the SP which returns the values as recordset and not assign them to OUTPUT values.

Your SP could also be shortened to:

ALTER PROCEDURE [dbo].[spTAYearSummary]
      @passedTaxAuthorityID int = 0,
      @passedFromYear       int = 0,
      @passedThruYear       Int = 9999
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      SELECT  
         CAST(ISNULL(SUM(FaceBal),0) AS decimal(10,2)) AS FaceSum,
         CAST(ISNULL(SUM(PenaltyBal),0) AS decimal(10,2)) AS PenaltySum,
         CAST(ISNULL(SUM(InterestBal),0) AS decimal(10,2)) AS InterestSum,
         CAST(ISNULL(SUM(LienCostBal),0) AS decimal(10,2)) AS LienSum,
         CAST(ISNULL(SUM(SvcChgBal),0) AS decimal(10,2)) AS SvcChgSum
      FROM tblTaxYears
      WHERE TaxAuthorityRecID =  @passedTaxAuthorityID
      --  and TaxYear Between @passedFromYear and @passedThruYear
         --PayStatusID <> 1 and
END

Select allOpen in new window

(And as this now only consists of a single SELECT statement you could also rewrite that as table UDF which makes it possible to be used in a view. This gives more flexibility as an SP because with an SP you could only use INSERT...EXECUTE to insert the returned table into another one whereas an UDF can be used in a view with CROSS APPLY (like INNER JOIN) or OUTER APPLY (like LEFT JOIN) to use values of another table as parameters for the UDF.)

The method of retrieving OUTPUT variables I wrote at the beginning I would only use if you cannot change the SP of the database and must use what you have there. If you can develop the SPs on your own, avoid them if there is no real need or use ADO which makes it easy to get the parameters back. You already have a code for that.

Cheers,

Christian

Christian, Thanks very much, that is all great information.  I am the one creating the SPROCS so they can be however I like.  Since I am such a NUB to SQL Server I am creating them based on examples I find on the web.  Is it more efficient a recordset rather than 'Output' Variables?

I've played with my code since your post but still am not doing it correctly, surely because I am so remedial on the subject.  I can't get over the first stumbling block of executing a passthru query that returns one value.


1. Could you please revise my original code attempting to uses a passthru query to execute the SPROC that is passed one value and returns one value.  
Here is the SPROC

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetNextReceiptNum]    Script Date: 4/25/2017 11:03:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetNextReceiptNum] 
	-- Add the parameters for the stored procedure here
	@MuniCode           int = 0, 
	@NewReceiptNumber   int = 0    Output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @WkReceiptNum	int   = 0,
	        @NextReceipt_C  Int   = 0

 -- Retrieve NextReceipt_C

        SELECT
           @NextReceipt_C = NextReceipt_C
        FROM
           tblMuni_Master
        Where
           MuniCode = @MuniCode

    -- Add 1 to the Receipt Number

       SET @NextReceipt_C = @NextReceipt_C + 1
       SET @NewReceiptNumber = @NextReceipt_C

   -- Update the Table

        UPDATE
           tblMuni_Master
        SET
           [NextReceipt_C] = @NextReceipt_C
        WHERE
           [MuniCode] = @MuniCode


END

Select allOpen in new window

Here is my latest non-working attempt at executing the SP using a passthru query.  The intended result of this would be to have the to have the SP returned value in the MS Access variable 'returnNextReceipt '.

Private Sub EEPassThru_ExecuteSproc_ValueReturned()

' the connection will vary based on test or production
' in test it will vary even by userID

gConnection = "ODBC;Description=JTS Data;DRIVER=SQL Server;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"

Dim Muni As Long
Dim returnNextReceipt As Long
'
Dim db As DAO.Database
Set db = getCurrentDbC
Dim rs As DAO.Recordset
'
Muni = 877
returnNextReceipt = 40
'
With DBEngine(0)(0).CreateQueryDef("")
         .Connect = gConnection
         .ReturnsRecords = True
         .SQL = "EXEC dbo.spGetNextReceiptNum"
         .SQL = .SQL & "  @MuniCode         = " & Muni
         .SQL = .SQL & ", @NewReceiptNumber = " & returnNextReceipt & " OUTPUT"
         .SQL = .SQL & " SELECT @NewReceiptNumber  as 'returnNextReceipt' "
        ' .Execute dbSQLPassThrough + dbFailOnError
End With
'
'set rs = .OpenRecordset instead of .Execute where rs is a recordset variable
End Sub

Select allOpen in new window

As you can see I've been commenting and uncommenting different lines of code to see what happened

Perhaps when I see the working solution I'll get some general understanding.

I haven't used UDF's yet so I'd like to keep this as Stored Procedures for now.

Christian, thank you so much.  I wasn't getting that the example code you gave was in an actual Passthru query rather than the querydef construct I started with.

I worked thru your response starting with your own examples.  I set up the new SP, the new passthru query, functions 'execSP_OUTPUT' and 'ExecuteSPOutTest'.  Everything worked perfectly and the returned values were show in the immediate window.

Next I revised my sproc 'spGetNextReceiptNum' as you suggested and created the function

Public Sub ExecuteSPGetNextReceiptNum()
    Dim f As DAO.Field
    Dim rs As DAO.Recordset
    Dim colParams As Collection
    Set colParams = New Collection
    colParams.Add 877, "@MuniCode"
    Set rs = execSP_OUTPUT("ptQryGetNextMuniReceiptNumber", colParams)
    Debug.Print rs.Fields("@NewReceiptNumber")
    
    
End Sub

Select allOpen in new window

When I execute the above function I get this error message:

Here is my Sproc

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetNextReceiptNum]    Script Date: 4/25/2017 11:06:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetNextReceiptNum] 
	-- Add the parameters for the stored procedure here
	@MuniCode           int = 0, 
	@NewReceiptNumber   int = 0    Output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE
	   tblMuni_Master
	SET
	   NextReceipt_C = NextReceipt_C + 1
	WHERE
	   MuniCode = @MuniCode;

	SET @NewReceiptNumber = ISNULL((SELECT NextReceipt_C FROM tblMuni_Master WHERE MuniCode = @MuniCode), 0);
END

Select allOpen in new window

This is my passthru query 'ptQryGetNextMuniReceiptNumber'

DECLARE @NextReceipt 	AS 	int;

EXECUTE EXEC dbo.spGetNextReceiptNum @MuniCode                 = {@MuniCode},
                                                                        @NewReceiptNumber = @NextReceipt OUTPUT;

SELECT @NewReceiptNumber AS '@NewReceiptNumber';

Select allOpen in new window

Any idea what is wrong.  I'm pretty sure my code matches yours.

Thanks again.  I made the change but I am still getting the same error.  I put a debug.print in 'Function execSP_OUTPUT' to display 'qd.SQL' after it is built. This is the result:

DECLARE @NextReceipt    AS  int;

EXECUTE dbo.spGetNextReceiptNum @MuniCode =  877,
                                                               @NewReceiptNumber = @NextReceipt OUTPUT;

SELECT @NewReceiptNumber AS '@NewReceiptNumber';

Select allOpen in new window

I also checked for tabs or special characters embedded and none showed in Notepad.

I also ran the original ADO code that executed this Sproc make sure the SP was functioning properly and it is.

Any ideas?

Christian, you are awesome.  I put the error code in the routine and got [Microsoft].[ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@NextReceiptNumber".

I changed the PT Query to look like this:

DECLARE @NewReceiptNumber 	AS 	int;

EXECUTE dbo.spGetNextReceiptNum @MuniCode =  {@MuniCode},
                                                               @NewReceiptNumber = @NewReceiptNumber OUTPUT;

SELECT @NewReceiptNumber AS '@NewReceiptNumber';

Select allOpen in new window

and it worked perfectly.

In your last post you mention << (please make sure that the QueryDef is restored to the original SQL value otherwise you loose the "variables").>>

What does that mean and how do I accomplish it?  I check my PT query after execution and it looks the same as before execution.

That error code will be handy. The dreaded error 3146 !!

Thank you for all of your help and explanations.  I actually understand this now, sort of.

Nice job Christian.

@ mlcktmguy
So just to summarize ... you are still calling the SP from VBA?
And returning one value ?

If so ... what is the VBA code you have now?

Asking because I'm sure I'll need to do this soon,

Here's the final after Christians 'help', meaning most of the code is his.  I was pretty much along for the ride.

SPROC

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetNextReceiptNum]    Script Date: 4/26/2017 3:19:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetNextReceiptNum] 
	-- Add the parameters for the stored procedure here
  	@MuniCode           int = 0, 
	@NewReceiptNumber   int = 0    Output
   
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE
	   tblMuni_Master
	SET
	   NextReceipt_C = NextReceipt_C + 1
	WHERE
	   MuniCode = @MuniCode;

	SET @NewReceiptNumber = ISNULL((SELECT NextReceipt_C FROM tblMuni_Master WHERE MuniCode = @MuniCode), 0);
END

Select allOpen in new window

PassThru Query named 'ptQryGetNextMuniReceiptNumber'

DECLARE @NewReceiptNumber 	AS 	int;

EXECUTE dbo.spGetNextReceiptNum @MuniCode =  {@MuniCode},
                                                               @NewReceiptNumber = @NewReceiptNumber OUTPUT;

SELECT @NewReceiptNumber AS '@NewReceiptNumber';

Select allOpen in new window

Function to Format things to call the stored procedure

Public Function ExecuteSPGetNextReceiptNum(passedMuniCode As Long) As Long
On Error GoTo ErrorTrap

Dim wkReturnReceipt As Long

    Dim f As DAO.Field
    Dim rs As DAO.Recordset
    Dim colParams As Collection
    Set colParams = New Collection
    colParams.Add passedMuniCode, "@MuniCode"
    Set rs = execSP_OUTPUT("ptQryGetNextMuniReceiptNumber", colParams)
    'Debug.Print rs.Fields("@NewReceiptNumber")
    '
    If rs.EOF Then
        ExecuteSPGetNextReceiptNum = 0
        '
    Else
        ExecuteSPGetNextReceiptNum = Nz(rs![@NewReceiptNumber])
    End If
    '
    rs.Close
    Set rs = Nothing
    
    
Exit_errortrap:
    Exit Function
    
ErrorTrap:
    Dim myerror As Error
    For Each myerror In DBEngine.Errors
        With myerror
            If .Number <> 3146 Then
                MsgBox .Description
            End If
        End With
    Next
    Resume Exit_errortrap  
    
End Function

Select allOpen in new window

Christian's function that handles building the execution string using any number of parameters

Public Function execSP_OUTPUT(strQueryName As String, colParams As Collection) As DAO.Recordset
    
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    Dim strParamName As String
    Dim lngPos As Long
    
   On Error GoTo execSP_OUTPUT_Error
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQueryName)

    strSQL = qd.SQL
    
    Do
        lngPos = InStr(qd.SQL, "{")
        If lngPos > 0 Then
            strParamName = Mid(qd.SQL, lngPos + 1, InStr(lngPos, qd.SQL, "}") - 1 - lngPos)
            qd.SQL = Replace(qd.SQL, "{" & strParamName & "}", colParams(strParamName))
        End If
    Loop Until lngPos = 0
    
 '   Debug.Print qd.SQL
    
    Set execSP_OUTPUT = qd.OpenRecordset() 'dbOpenForwardOnly, dbReadOnly Or dbFailOnError)

execSP_OUTPUT_Exit:
    If strSQL <> "" And Not qd Is Nothing Then
        qd.SQL = strSQL ' Restore the variable parameters
    End If
    Set qd = Nothing
    Set db = Nothing
    Exit Function

    
execSP_OUTPUT_Error:
    Dim myerror As Error
    For Each myerror In DBEngine.Errors
        With myerror
            If .Number <> 3146 Then
                MsgBox .Description
            End If
        End With
    Next
    Resume execSP_OUTPUT_Exit
    
    ' orig
    Select Case Err.Number
        Case Else
            MsgBox "Error in Modul: modTest.Function: execSP_OUTPUT" & vbCrLf & _
                   Err.Description & vbCrLf & _
                   "Error: " + Str(Err.Number), vbOKOnly Or vbExclamation, "Error"
    End Select
    Resume execSP_OUTPUT_Exit
End Function

Select allOpen in new window

That's everything.

Very cool. Thanks for posting ... and thank you Christian for jumping with the help and solution !