none
Returning value from stored proc execution RRS feed

  • Question

  • Hi

    I have a stored proc like below;

    CREATE PROCEDURE [dbo].[InsertApplicants](@RowCount as int output) 
    AS
    BEGIN
    BEGIN
    INSERT INTO Applicants(...)
    SELECT ...       
    FROM   MyTable1
    WHERE  ...
    SELECT @RowCount=@@ROWCOUNT
    END
    
    END
    
    I am executing the stored proc using code below and trying to return the value of the parameter RowCount back as well. However I am getting 0 (zero) every time. 

            Cmd.CommandType = CommandType.StoredProcedure
            RowCount = Cmd.Parameters.Add("@RowCount ", System.Data.SqlDbType.Int)
            RowCount.Direction = System.Data.ParameterDirection.Output
    
            i = -1
            Try
    
                Try
    
                    Dim result = ExecuteSQLAsync(Cmd)
                    i = CInt(RowCount.Value)
    
                Catch ae As AggregateException
    
                    For Each ex In ae.InnerExceptions
                        Throw
                    Next
    
                End Try
    
    
            Catch ex As Exception
                Exit Function
    
            End Try
    
        ...
    
        Async Function ExecuteSQLAsync(DBCommand As SqlCommand) As Task(Of Integer)
    
            Dim x = Await DBCommand.ExecuteNonQueryAsync()
    
            Return x
    
        End Function

    How can I get the correct value for RowCount please?

    Thanks

    Regards

    Thursday, June 19, 2014 1:28 PM

Answers

  • Hello,

    How do you call your stored procedure in your client side? Do you add the “out” keyword after the output type parameter? Here, with your provided code, I made a shorten demo which can return the correct row count:

    The stored procedure:

    CREATE PROCEDURE [dbo].[ProGetOrder]
    
    @RowCount int output
    
    AS
    
           select * from [Order]
    
           select @RowCount = @@ROWCOUNT
    
    RETURN 0
    

    The client side code:

    string connectionString = @"Data Source=(localdb)\Projects;Database=DFDB;";
    
    
                SqlConnection connection = new SqlConnection(connectionString);
    
    
                try
    
                {
    
                    if (connection.State == ConnectionState.Closed)
    
                    {
    
                        connection.Open();
    
                    }
    
    
                    DataTable dtOrder = new DataTable("Order");
    
    
                    SqlCommand cmd = new SqlCommand("ProGetOrder @RowCount out", connection);
    
    
                    SqlParameter RowCount = cmd.Parameters.Add("@RowCount", System.Data.SqlDbType.Int);
    
    
                    RowCount.Direction = System.Data.ParameterDirection.Output;
    
    
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
    
                    da.Fill(dtOrder);
    
    
                    int result = Convert.ToInt32(RowCount.Value);
    
                }
    
                catch (Exception)
    
                {
    
    
                    throw;
    
                }
    
                finally
    
                {
    
                    connection.Close();
    
                }
    

    Please have a try and if this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 20, 2014 1:50 AM
    Moderator