none
Return parameter with Pass-Through query RRS feed

  • Question

  • Hi,

    How could I get the return value from back-end SQL Server procedure?? How should I call the procedure using DAO to achieve this please?

    Regards,
    K
    Friday, March 3, 2017 1:27 PM

Answers

  • If the stored procedure returns data via a SELECT statement, you can just use a normal pass-through query to invoke it.  The pass-through query should have its ReturnsRecords property set to Yes.

    If the stored procedure returns a single data value through some other means, you can create a pass-through query that invokes the procedure, then SELECTs that value (so it returns a single-row, single-column result set).  Your pass-through query will thus contain more than one SQL statement, but this is valid in a pass-through query.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KhurramKZ Saturday, March 4, 2017 8:07 AM
    Friday, March 3, 2017 4:50 PM

All replies

  • It depends upon what your SQL Server stored procedure is returning. Is it a single output parameter? Is it a set of rows? I generally use ADO, but its possible to use DAO as well depending upon what is being returned by the sp.

    https://docs.microsoft.com/en-us/sql/ado/guide/data/calling-a-stored-procedure-with-a-command


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 3, 2017 1:50 PM
  • If the stored procedure returns data via a SELECT statement, you can just use a normal pass-through query to invoke it.  The pass-through query should have its ReturnsRecords property set to Yes.

    If the stored procedure returns a single data value through some other means, you can create a pass-through query that invokes the procedure, then SELECTs that value (so it returns a single-row, single-column result set).  Your pass-through query will thus contain more than one SQL statement, but this is valid in a pass-through query.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KhurramKZ Saturday, March 4, 2017 8:07 AM
    Friday, March 3, 2017 4:50 PM
  • Thanks Dirk, worked for me )
    Saturday, March 4, 2017 8:07 AM