Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    27

    Unanswered: Return Value of Stored Procedure

    I have a Stored Procedure in SQL Server and it returns a value which is integer. I want to use this
    return value in the "SELECT" statement of my Servlet .

    CREATE PROCEDURE Proc1

    AS



    RETURN @ret

    In my servlet i want to use the Return value of the Proc1 i..e, @ret..How can this be done?

  2. #2
    Join Date
    Feb 2003
    Posts
    6

    Re: Return Value of Stored Procedure

    Originally posted by newbie03
    I have a Stored Procedure in SQL Server and it returns a value which is integer. I want to use this
    return value in the "SELECT" statement of my Servlet .

    CREATE PROCEDURE Proc1

    AS



    RETURN @ret

    In my servlet i want to use the Return value of the Proc1 i..e, @ret..How can this be done?

    Try this:
    EXEC @rc = Proc1

  3. #3
    Join Date
    Feb 2003
    Posts
    27
    Should i declare "@rc" in my servlet. so that i can access it later ?

  4. #4
    Join Date
    Feb 2003
    Posts
    27
    My Java Code is


    String query = " exec @rc = Proc1 'parameter1', 'parameter2' "
    Statement.executeUpdate(query);

    I get error "Must declare the variable @rc". If i declare "rc" as integer in java


    int rc
    String query = " exec "+rc+
    " = Proc1 'parameter1', 'parameter2' "
    Statement.executeUpdate(query);

    I get error :"Incorrect Syntax near rc.

  5. #5
    Join Date
    Feb 2003
    Posts
    6
    Originally posted by newbie03
    My Java Code is


    String query = " exec @rc = Proc1 'parameter1', 'parameter2' "
    Statement.executeUpdate(query);

    I get error "Must declare the variable @rc". If i declare "rc" as integer in java


    int rc
    String query = " exec "+rc+
    " = Proc1 'parameter1', 'parameter2' "
    Statement.executeUpdate(query);

    I get error :"Incorrect Syntax near rc.

    Try this as well:

    Create Procedure Proc1 (rec int OUTPUT)
    as
    .....

    I don't have the java environment setup, but I tried with VB+ADO and got result back.

  6. #6
    Join Date
    Feb 2003
    Posts
    27
    I tried using the CallableStatement instead of Statement. CallableStatement provides way to call stored procedures from the databse. It seems to work.. Thank you for the help.

  7. #7
    Join Date
    Feb 2003
    Posts
    27
    Hi sunburn,

    I got it working in JAVA, but having problem running the same stored procedure from VB. Can u explain how to run the same stored procedure from VB using ADO. Following is the Stored Procedure.

    CREATE PROCEDURE Proc1
    @Parameter1 varchar(50),
    @Parameter2 varchar(50),
    @Parameter3 int OUTPUT

    AS

    declare
    @ret varchar(50)

    select @Parameter1 = LTRIM(RTRIM(@Parameter1))
    select @Parameter2 = LTRIM(RTRIM(@Parameter2))

    exec master..xp_SHA1 @Parameter2 , @ret OUTPUT
    select @Parameter3 = count(*) from Table where col1 = @Parameter1 and col2 = @ret

    In VB code i should be able to capture the @Parameter3 value.

    Thanks

  8. #8
    Join Date
    Feb 2003
    Posts
    6
    Originally posted by newbie03
    Hi sunburn,

    I got it working in JAVA, but having problem running the same stored procedure from VB. Can u explain how to run the same stored procedure from VB using ADO. Following is the Stored Procedure.

    CREATE PROCEDURE Proc1
    @Parameter1 varchar(50),
    @Parameter2 varchar(50),
    @Parameter3 int OUTPUT

    AS

    declare
    @ret varchar(50)

    select @Parameter1 = LTRIM(RTRIM(@Parameter1))
    select @Parameter2 = LTRIM(RTRIM(@Parameter2))

    exec master..xp_SHA1 @Parameter2 , @ret OUTPUT
    select @Parameter3 = count(*) from Table where col1 = @Parameter1 and col2 = @ret

    In VB code i should be able to capture the @Parameter3 value.

    Thanks

    Here is my quick test:

    Dim gconn As ADODB.Connection
    Dim gCommand As ADODB.Command

    Private Sub Command1_Click()
    Set gconn = New ADODB.Connection
    Set gRS = New ADODB.Recordset
    gconn.ConnectionString = "driver={SQL Server};server=xxxxx;database=northwind;uid=sa;pwd =password"
    gconn.Open
    If gconn.State = 1 Then
    Set gCommand = New ADODB.Command
    Set gCommand.ActiveConnection = gconn
    gCommand.CommandType = 4
    gCommand.CommandText = "dbo.Proc1"
    gCommand.Parameters.Append gCommand.CreateParameter("Parameter1", adVarChar, adParamInput, 50)
    gCommand.Parameters.Append gCommand.CreateParameter("Parameter2", adVarChar, adParamInput, 50)
    gCommand.Parameters.Append gCommand.CreateParameter("Parameter3", adInteger, adParamOutput, 4)
    gCommand.Execute
    MsgBox gCommand.Parameters.Item("Parameter3")
    End If
    gconn.Close
    Set gconn = Nothing

    End Sub

    Try it and good luck!

  9. #9
    Join Date
    Feb 2003
    Posts
    27
    That worked like a charm. Thank you so much for the help

    Thanks

Posting Permissions

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