SQL - cmd.ExecuteReader() // cmd.ExecuteNonQuery() differences // stored procedure query

Question

Hello,

I receive an SQL database script from the customer.
A stored procedure query should return exactly one data record.
I don't know the names of the columns. Only the index. I will receive the script in November. I would like to prepare it. Perhaps you could give me some tips.

Perhaps a call to everyone to inquire possible ways of reading the values. That would be great, thanks in advance.

// ### I know that. ###
//ExecuteReader
//Do not use: when database query is going to provide for sure exactly 1 record.
//Use: when database query is going to provide a set of records.It may be search or report.
//ExecuteNonQuery
//Use: when we are talking about a single database record - in Update, Insert, Delete and Get by Id.
cmd.Parameters.AddWithValue("@returnCode", retCode);
cmd.Parameters.AddWithValue("@Device", "White group");
cmd.Parameters.AddWithValue("@NumCount", numCount);

using (var rdr = cmd.ExecuteReader())
{
	if (rdr.HasRows)
	{
		while (rdr.Read())
		{
			firstIdentifier= (int)rdr[0];
			lastIdentifier= (int)rdr[1];
			numCountResponse = (int)rdr[2];
			

// @ReturnVal could be any name
var returnParameter = cmd.Parameters.Add("@returnCode", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;

//cmd.Connection = con;
//con.Open();
cmd.ExecuteNonQuery();

firstIdentifier= (int)cmd.Parameters[0].Value;
lastIdentifier= (int)cmd.Parameters[1].Value;
numCountResponse = (int)cmd.Parameters[2].Value;

• How is the return code handled?-
• Is it a separate field in the request?
• Or does the procedure return this value?
• What is it called? Can the name be different?

Answer 1

If the stored procedure is guaranteed to return exactly one record, then using ExecuteReader() with a while loop is not necessary — you can use a single if (rdr.Read()) or even ExecuteScalar() if you only expect one value.

However, since you mentioned you don’t know the column names yet (only the index positions), preparing your code to handle results by index is a good idea. Here’s how you can structure it safely:

using (SqlCommand cmd = new SqlCommand("YourStoredProcedureName", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@Device", "White group");
    cmd.Parameters.AddWithValue("@NumCount", numCount);

    // Return value parameter
    var returnParameter = cmd.Parameters.Add("@returnCode", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    using (var rdr = cmd.ExecuteReader())
    {
        if (rdr.Read())  // Only one record expected
        {
            var firstIdentifier = rdr.GetInt32(0);
            var lastIdentifier = rdr.GetInt32(1);
            var numCountResponse = rdr.GetInt32(2);
        }
    }

    int returnCode = (int)returnParameter.Value;
    Console.WriteLine($"Return Code: {returnCode}");
}

How the return code works

The return value (@returnCode) is not part of the result set.

It is a special output parameter returned by the stored procedure itself (via a RETURN statement).

You can name it anything you like in your C# code — the important part is setting

returnParameter.Direction = ParameterDirection.ReturnValue;

Inside your SQL stored procedure, you’ll usually see something like:

CREATE PROCEDURE YourStoredProcedure
    @Device NVARCHAR(50),
    @NumCount INT
AS
BEGIN
    -- logic here
    RETURN 1  -- or any integer
END

So, to summarize:

Use ExecuteReader() if you’re reading fields from a record.

Use ParameterDirection.ReturnValue to capture the procedure’s return code.

• The return value is not a column — it’s a separate output from the stored procedure.If the stored procedure is guaranteed to return exactly one record, then using ExecuteReader() with a while loop is not necessary — you can use a single if (rdr.Read()) or even ExecuteScalar() if you only expect one value. However, since you mentioned you don’t know the column names yet (only the index positions), preparing your code to handle results by index is a good idea. Here’s how you can structure it safely:

    using
  

  How the return code works
  • The return value (@returnCode) is not part of the result set.
  • It is a special output parameter returned by the stored procedure itself (via a RETURN statement).
  • You can name it anything you like in your C# code — the important part is setting

        returnParameter.Direction = ParameterDirection.ReturnValue;
    

  • Inside your SQL stored procedure, you’ll usually see something like:

        CREATE
    

  So, to summarize:
  • Use ExecuteReader() if you’re reading fields from a record.
  • Use ParameterDirection.ReturnValue to capture the procedure’s return code.
  • The return value is not a column — it’s a separate output from the stored procedure.

Answer 2

A sql query returns rows. Each row has columns. The order of the columns depends on the select query. The column data type is dependent on the data type of the column in the select.

when you call a stored proc, it can return a single integer return value available after all the result sets for that query have been processed. The name of this parameter is defined by the caller (your code), the parameter direction property defines the return value parameter. Only a parameter of type return value is updated by the query. The rest are read only and are unmodified by calling the query.

ExecuteNonQuery() is used when your code does not need any of the row data returned. It will read any rows and toss the results. After calling ExecuteNonQuery() you can access the return value.

you current code runs an undefined query (command text is not set) and reads the first 3 column which must exist and be ints. Then it runs the query again and accesses the return value, and the parameter values set previously in the code.

generally return values are only used to return a stored proc completion status and are only available if the query is a stored proc call.

if the customer query is a returning one data row, it very likely they are not returning a stored proc return value also.

while the name of the column is not important, you need the column number and datatype of each column to read. If they just give you the column names and datatype you will need to use the columns collection to map the column name to column number.