Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Question Unanswered: How to return a value from SP

    Hi all,

    How to return a value from a store procedure?

    I use a VBA to call a store procedure, but I would like to be able to return the result back to a variable.

    Here is an VBA example:

    Dim GetDestinationID As Long

    Dim conConnection As ADODB.Connection
    Dim StrSQL As String

    Set conConnection = CurrentProject.Connection

    StrSQL = "usp_GetDestinationID " & 2 & ", " & _
    GetDestinationID

    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

    I would like to return GetDestinationID.

    Here is the SP:

    CREATE PROCEDURE [dbo].[usp_GetDestinationID]

    (
    @intOrderID int,
    @intDestinationID int=0 OUTPUT

    )

    AS

    BEGIN

    set @intDestinationID=(SELECT lv.DestinationID
    FROM [Land Voyages] AS lv INNER JOIN [Pickup Booking List] AS pbl
    ON lv.LandVoyageID=pbl.LandVoyageID
    WHERE pbl.OrderID = @intOrderID)

    END
    GO

    What is wrong? Can I return a value to a Visual Basic Application from a Store Procedure?

    Regards

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: How to return a value from SP


  3. #3
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Talking VBA calling an SP and returning a value

    HI all

    I found a solution to my question about VBA calling a Stored Procedure and returning a value

    VBA:
    '-------------------------------------------------------------
    Private Function GetDestinationID(OrderID As Long) As Long
    On Error GoTo GetDestinationID_Err

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command

    With cmd

    .ActiveConnection = CurrentProject.Connection
    .CommandText = "usp_GetDestinationID"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@intOrderID", adInteger, adParamInput, , OrderID)
    .Parameters.Append .CreateParameter("@intDestinationID", adInteger, adParamOutput)
    .Execute
    GetDestinationID = .Parameters("@intDestinationID").Value
    End With

    WrapUp:


    Exit_GetDestinationID:
    Set cmd = Nothing
    Exit Function

    GetDestinationID_Err:
    Call LogMsgError(Err.Description, Err.Number, ModuleName$, "GetDestinationID")
    Resume Exit_GetDestinationID
    End Function

    '-----------------------------------------------------
    T-SQL:

    CREATE PROCEDURE dbo.usp_GetDestinationID

    (
    @intOrderID int,
    @intDestinationID int=0 OUTPUT
    )

    AS

    SET NOCOUNT ON

    BEGIN

    SELECT @intDestinationID=lv.DestinationID
    FROM dbo.[Land Voyages] AS lv INNER JOIN dbo.[Pickup Booking List] AS pbl
    ON lv.LandVoyageID=pbl.LandVoyageID
    WHERE pbl.OrderID = @intOrderID

    END
    GO
    '------------------------------------

    Thanks to Igor for suggestions.

    Dani

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •