Get return value from select stored procedure

Question

How do you get the return value from a select stored procedure, i.e number of records returned.

The value I'm after is RETURN ( SELECT COUNT( * ) FROM #Eligible_Ids );

The only way I have been able to do it so for is as follows:

DECLARE @NumberOfResults INT

SET @NumberOfResults = (SELECT COUNT( * ) FROM #Eligible_Ids)

Then in select statement i have NumberResults = @NumberOfResults but this adds an extra row to the data retuned.

My code in class is

 using (SqlCommand cmd = new SqlCommand(spName, cn))
                {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@strSearchPhrase", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@SearchMode", SqlDbType.Int, 4));
                cmd.Parameters.Add(new SqlParameter("@intPageNumber", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@intRecordsPerPage", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@intTotalRecordsReturned", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int));
                cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                cmd.Parameters["@strSearchPhrase"].Value = q;
                cmd.Parameters["@SearchMode"].Value = 1;
                cmd.Parameters["@intPageNumber"].Value = pagenumber;
                cmd.Parameters["@intRecordsPerPage"].Value = 10;
                cmd.Parameters["@intTotalRecordsReturned"].Value = 10;

                cn.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default))
                    {
                    if (rdr.HasRows)
                        {
                        while (rdr.Read())
                            {
                            Data.Add(new DisplaySearchResults
                            {
                                Title           = (string)rdr["PageTitle"],
                                Description     = (string)rdr["PageParagraph"],
                                URL             = (string)rdr["PageURL"],
                                returnvalue     = (int)rdr["NumberResults"]
                            });
                            }
                        }


                    return Data;

Any help on doing this the correct way would be appreciated

George

Well.. my friend did you consider of using Entity framework to ease the pain that you are taking at the moment. You can map stored procedures as well, I you have to. It is a better way of doing this. Thanks — diyoda_, Feb 26 '13 at 18:33
CareerChange I would suggest changing your cmd.Parameters to the following `cmd.Parameters.AddWithValue("@strSearchPhrase", paramvalue); etc... let the server handle the datatype resolving — MethodMan, Feb 26 '13 at 18:34
Check something called 'Output parameter'. Here is an example of usage: http://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net — MarcinJuraszek, Feb 26 '13 at 18:47
Please show all of the TSQL code. Multiple `SELECT`s may be producing multiple recordsets. — HABO, Feb 26 '13 at 18:48
Hi DJ Kraze, do you mean cmd.Parameters.AddWithValue("@strSearchPhrase",100); — CareerChange, Feb 26 '13 at 18:49
Hi MarcinJuraszek I have tried that, I have been getting outofrangeexceptions or nullreferanceexceptions, I just need to work out where to put the return value, as I'm changing code — CareerChange, Feb 26 '13 at 18:52

score 2 · Answer 1 · answered Feb 26 '13 at 19:11

To get the return value, you add a parameter with direction ReturnValue. Since you have already done this, it is just:

var val = cmd.Parameters["@RETURN_VALUE"].Value;

Important: you can only read this after you have read all the result rows from the reader: parameter values and return values are at the end of the TDS stream.

score 2 · Accepted Answer · edited May 23 '17 at 10:24

See this

And in your specific case alter this in c# Coding

cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int));
cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

to this

cmd.Parameters.Add(new SqlParameter("@NumberOfResults", SqlDbType.Int));
cmd.Parameters["@NumberOfResults"].Direction = ParameterDirection.Output;

and in SotoredProcedure instead of declaring

DECLARE @NumberOfResults INT

add an output parameter

@NumberOfResults INT output

and then you can read the value in @NumberofResults parameter like this

TextBox1.Text = cmd.Parameters["@NumberOfResults"].Value.ToString();

Dustin Kingen · Answer 3 · 2013-02-26T19:16:50.393

Try @@ROWCOUNT

--Simple T-Sql Proc

CREATE PROCEDURE usp_OutputRowExample
    @Count INT OUTPUT
AS
BEGIN
    SELECT * FROM <table>; --Get your entities

    SELECT @Count = @@ROWCOUNT; --@@ROWCOUNT returns the amount of
                                --affected rows from the last query
END

//From C#

public Tuple<IEnumerable<object>, int>> GetObjects()
{
    using(var connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        using(var command = sqlConnection.CreateCommand())
        {
            command.CommandText = "usp_OutputRowExample";
            command.CommandType = CommandType.StoredProcedure;

            var outputParameter = new SqlParameter("@Output", SqlDbType.Int)
                { Direction = ParameterDirection.Output };

            command.Parameters.Add(outputParameter);

            using(var reader = command.ExecuteReader())
            {
                var entities = new List<entities>();
                while(reader.Read())
                {
                    //Fill entities
                }
            }

            var outputCount = outputParameter.Value as int? ?? default(int);

            return new Tuple<IEnumerable<object>, int>(entities, outputCount);
        }
    }
}

Get return value from select stored procedure

3 Answers3