• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 716
  • Last Modified:

SQL Stored Procedure return values

How do i get the return value from a stored procedure, I know the sql is:

exec @ret = sp_check

but i would like to get the return value so that access can respond to it.
  • 2
  • 2
  • 2
1 Solution
what we do is return the code via a select and store the results in a resultset. I don't beleieve DAO can handle return values from stored procedures. ADO can but that would mean adding an ado reference to you Access code.
simonsabin is partially correct.  However, without adding any new references, you can use ODBCDirect to behave "similar" to ADO in that you can pass output parameters and then see them come back.

For example (this is borrowed heavily from "SQL Server 7 Developer's Guide"):
Private Sub CallQDsp()
    Dim qd As QueryDef
    Dim sConnect As String
    Dim sSQL As String, sServer As String
    Dim sLoginID As String, sPassword As String
    Dim cn As Connection
    Dim ws As Workspace
    sLoginID = "sa"
    sPassword = vbNullString
    sServer = "DAREK"
    'The type parameter sets the workspace to ODBCDirect
    Set ws = DBEngine.CreateWorkspace("NewODBCDirectWS", sLoginID, _
                sPassword, dbUseODBC)

    'Set the default Workspace type to ODBCDirect
    DBEngine.DefaultType = dbUseODBC
    'Define the DSN-less connection
    sConnect = "ODBC;Driver=SQL Server;UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";Server=" & sServer & _
    'Open the new connection
    Set cn = ws.OpenConnection("", dbDriverComplete, False, sConnect)
    'Create the new procedure
    sSQL = "Create Proc CountStateRows " _
        & "(@state char(2), @rows Int Output) As " _
        & "Select @rows = Count(*) From authors Where state = @state"
    cn.Execute sSQL
    'Call the procedure
    sSQL = "{ Call CountStateRows (? ,?) }"
    Set qd = cn.CreateQueryDef("", sSQL)
    qd.Parameters(0).Value = "UT"
    qd.Parameters(1).Direction = dbParamOutput
    MsgBox "Number of rows: " & qd.Parameters(1).Value
End Sub

Obviously if you do use something like this you will want to make sure you cleanup your variables...
Thats output parameters not return values. very different.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Sorry, I misread the question...
SadManAuthor Commented:
Ethier way is OK, I can eaily re-write the stored proc, I think...

I'm going to try your answer dtomyn.
SadManAuthor Commented:
Thanks dtomyn, not exactly what I was after but changing the code allowed me to get the results.

It worked perfectly.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now