accessing output values from a SP

  • vanbunty

    Newbie

    Points: 3

    DB: MS SQL server 2k

    Lang: VB 6.0

    API: ADO2.6

    query:---

    I have a SP which accepts Inputs (empID, name,....,RETempID OUTPUT)

    This SP does eithter 1 of the below 2 operations --

    1.)UPdate - If empID already exists in the emp table & return the empID thro RETEmpID

    2.)Insert - If the empID deosnot exist in the table & return the New empID thro the RETEmpID

    Problem:

    Not able to access the OUTPUT parameter in VB

    but able to access it in SQL Query analyser

    please reply ASAP

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    How are you executing the procedure from your VB application? If you aren't already doing it, use a command-object and like this:

    cmd.Parameters.Append cmd.CreateParameter("@RETEmpID", adInteger, adParamOutput, 4, MyLongVariable)

    cmd.Execute()

    MyLongVariable = cmd.Parameters("@RETEmpID")

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

  • vanbunty

    Newbie

    Points: 3

    i am using the command object....& doing what u suggested

    after i execute the SP thro the command.execute method....& check for the number of recs affected...it shows properly....but this method is not returning the recordset object....as it should.....& so i am not able to access the OUTPUT values from the SP....as the doc says that i need to close this recordset before accessing the OUTPUT values from the SP......

  • Andy Warren

    SSC Guru

    Points: 119676

    Why you care about a recordset if you're using output parameters? You normally use them to avoid creating a recordset.

    Andy

  • Leon Platt

    SSCommitted

    Points: 1921

    What does returning a recordset have to do with retreiving the output parameters? You do not access the output parameters via a recordset you access them as Chris as shown above with: MyLongVariable = cmd.Parameters("@RETEmpID")

    If you can't figure it out you may need to post a snipet of your code so we can see what you are doing.

  • vanbunty

    Newbie

    Points: 3

    Here is the SP i am using

    --------------------------------------------

    CREATE PROCEDURE dbo.sp_AddUpdateEmpTable

    @EmpName nvarchar(50),

    @EmpID int,

    @returnEmpID int output

    AS

    BEGIN

    If exists(select * from EmpTable where EmpID= @EmpID)

    BEGIN

    Update EmpTable

    Set EmpName=@EmpName

    Where EmpID = @EmpID

    select @returnEmpID= @EmpID

    END

    Else

    BEGIN

    Insert into EmpTable(EmpName)

    Values (@EmpName)

    select @returnEmpID= @@identity

    END

    END

    GO

    ---------------------------------------------

    Here is the VB code i am using to access the SP...

    NOTE: this is code is part of a COMplus

    component...but i have made changes so that it will run outside COMplus

    ---------------------------------------------

    Public Function AddUpdateEmpTable(ByVal EmpID As Integer, ByVal EmpName As String) As Integer

    On Error GoTo AddUpdateEmpTableErr

    Dim oAddUpdateEmpTableCon As New ADODB.Connection

    Dim oAddUpdateEmpTableRs As New ADODB.Recordset

    Dim oAddUpdateEmpTableCom As New ADODB.Command

    Dim ReturnEmpID As Integer

    Dim EmpIDPrm As Parameter, EmpNamePrm As Parameter, ReturnEmpIDPrm As Parameter, ReturnScenarioDProcCEIDIDPrm As Parameter

    Set oAddUpdateEmpTableCon = EstablishConnection

    Set ObjContext = GetObjectContext

    Set contextState = ObjContext

    Set oAddUpdateEmpTableCom.ActiveConnection = oAddUpdateEmpTableCon

    oAddUpdateEmpTableCom.CommandText = "sp_AddUpdateEmpTable"

    oAddUpdateEmpTableCom.CommandType = adCmdStoredProc

    Set EmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("EmpID", adInteger, adParamInput)

    oAddUpdateEmpTableCom.Parameters.Append EmpIDPrm

    EmpIDPrm.Value = EmpID

    Set EmpNamePrm = oAddUpdateEmpTableCom.CreateParameter("EmpName", 202, adParamInput,50)

    oAddUpdateEmpTableCom.Parameters.Append EmpNamePrm

    EmpNamePrm.Value = EmpName

    Set ReturnEmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("ReturnEmpID", adInteger, adParamOutput)

    oAddUpdateEmpTableCom.Parameters.Append ReturnEmpIDPrm

    ReturnEmpIDPrm.Value = ReturnEmpIDID

    Set oAddUpdateEmpTableRs = oAddUpdateEmpTableCom.Execute

    Debug.Print oAddUpdateEmpTableRs.State

    If oAddUpdateEmpTableRs.State = 1 Then

    oAddUpdateEmpTableRs.Close

    Debug.Print oAddUpdateEmpTableCom.Parameters("ReturnEmpID")

    AddUpdateScenarioDProcCEID = oAddUpdateEmpTableCom.Parameters("ReturnEmpID")

    Else

    Debug.Print oAddUpdateEmpTableCom.Parameters("ReturnEmpID")

    Err.Raise 9999, "cFactScenario:AddUpdateEmpTable Method", "AddUpdateEmpTable SP failed"

    End If

    oAddUpdateEmpTableCon.Close

    Set oAddUpdateEmpTableCon = Nothing

    Set oAddUpdateEmpTableCom = Nothing

    Set oAddUpdateEmpTableRs = Nothing

    Exit Function

    AddUpdateEmpTableErr:

    oAddUpdateEmpTableCon.Close

    Set oAddUpdateEmpTableCon = Nothing

    Set oAddUpdateEmpTableCom = Nothing

    Set oAddUpdateEmpTableRs = Nothing

    Debug.Print Err.Number & Err.Description & Err.Source

    Debug.Print Err.Number & Err.Description & Err.Source

    Call RaiseError(Err.Number, "cFactScenario:oAddUpdateEmpTable Method", Err.Description)

    End Function

    ---------------------------------------------

    really appreciate ur help

    rgds,

    van

  • jwiner

    SSCrazy

    Points: 2241

    you must return the value from your stored procedure:

    return @returnEmpID

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    I have two recommendations for you to solve this:

    1) Add SET NOCOUNT xx statements to your procedure (maybe not necessary, but I would do it anyway) so that your procedure looks like below. Then skip your recordset, so that your execute functionality looks like below.

    --------------------------

    CREATE PROCEDURE dbo.sp_AddUpdateEmpTable

    @EmpName nvarchar(50),

    @EmpID int,

    @returnEmpID int output

    AS

    BEGIN

    SET NOCOUNT ON

    If exists(select * from EmpTable where EmpID= @EmpID)

    BEGIN

    Update EmpTable

    Set EmpName=@EmpName

    Where EmpID = @EmpID

    select @returnEmpID= @EmpID

    END

    Else

    BEGIN

    Insert into EmpTable(EmpName)

    Values (@EmpName)

    select @returnEmpID= @@identity

    END

    SET NOCOUNT OFF

    END

    GO

    --------------------------

    VB code:

    <snip>

    Set ReturnEmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("ReturnEmpID", adInteger, adParamOutput)

    oAddUpdateEmpTableCom.Parameters.Append ReturnEmpIDPrm

    ReturnEmpIDPrm.Value = ReturnEmpIDID

    oAddUpdateEmpTableCom.Execute , , adExecuteNoRecords

    AddUpdateScenarioDProcCEID = oAddUpdateEmpTableCom.Parameters("ReturnEmpID")

    </snip>

    2. The other solution is to use return as Jonathan recommended. Change your procedure and VB code to below. I think you have to create your return parameter first in the VB code.

    --------------------------------------------

    CREATE PROCEDURE dbo.sp_AddUpdateEmpTable

    @EmpName nvarchar(50),

    @EmpID int

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @returnEmpID int

    If exists(select * from EmpTable where EmpID= @EmpID)

    BEGIN

    Update EmpTable

    Set EmpName=@EmpName

    Where EmpID = @EmpID

    select @returnEmpID= @EmpID

    END

    Else

    BEGIN

    Insert into EmpTable(EmpName)

    Values (@EmpName)

    select @returnEmpID= @@identity

    END

    SET NOCOUNT OFF

    RETURN @returnEmpID

    END

    GO

    ---------------------------------------------

    VB code:

    <snip>

    Set EmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("EmpID", adInteger, adParamReturnValue)

    oAddUpdateEmpTableCom.Parameters.Append EmpIDPrm

    EmpIDPrm.Value = AddUpdateScenarioDProcCEID

    Set EmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("EmpID", adInteger, adParamInput)

    oAddUpdateEmpTableCom.Parameters.Append EmpIDPrm

    EmpIDPrm.Value = EmpID

    Set EmpNamePrm = oAddUpdateEmpTableCom.CreateParameter("EmpName", 202, adParamInput,50)

    oAddUpdateEmpTableCom.Parameters.Append EmpNamePrm

    EmpNamePrm.Value = EmpName

    oAddUpdateEmpTableCom.Execute , , adExecuteNoRecords

    AddUpdateScenarioDProcCEID = oAddUpdateEmpTableCom.Parameters("RetVal")

    </snip>

    Actually, I don't think you need the row "EmpIDPrm.Value = AddUpdateScenarioDProcCEID", but my old code looks like that so it won't hurt...

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply