Stored Procedure Return

This section demonstrates calling stored procedures with ADO.NET: using ExecuteNonQuery to get the number of affected rows while capturing the stored procedure Return return value

1. Database Stored Procedure Definition

create procedure mynewproc(@id int)
as
begin
    declare @cout int
    -- Update statement
    update Article set title = '111' where id = @id
    -- Count records where id is greater than the input parameter
    select @cout = count(1) from Article where id > @id
    -- Return numeric value (SQL Server stored procedure return only supports int)
    return @cout
endCode language: PHP (php)

Important: return @cout is the return value of the stored procedure, which is different from the output output parameter.

2. Calling in C#

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("mynewproc", connection);
    command.CommandType = CommandType.StoredProcedure;

    // 【Critical】Register Return return‑value parameter, fixed name: ReturnValue
    SqlParameter para = new SqlParameter(
        "ReturnValue",
        SqlDbType.Int,
        4,
        ParameterDirection.ReturnValue,
        false,0,0,string.Empty,DataRowVersion.Default,null);
    command.Parameters.Add(para);

    // Stored procedure input parameter @id
    SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 8);
    param.Value = 4;
    param.Direction = ParameterDirection.Input;
    command.Parameters.Add(param);

    // ExecuteNonQuery: runs insert/update/delete, returns 【number of rows affected by update】
    int rowsAffected = command.ExecuteNonQuery();
    // Read value brought back by stored procedure return
    int result = (int)command.Parameters["ReturnValue"].Value;
}Code language: PHP (php)

Recommended Implementation

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("mynewproc", conn))
{
    conn.Open();
    cmd.CommandType = CommandType.StoredProcedure;

    // Register return‑value parameter (fixed name: ReturnValue, direction must be ReturnValue)
    cmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    // Input parameter
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = 4;

    // rowsAffected = how many records modified by update statement
    int rowsAffected = cmd.ExecuteNonQuery();
    // Fetch value from stored procedure return @cout
    int procReturnVal = (int)cmd.Parameters["ReturnValue"].Value;
}Code language: JavaScript (javascript)

Notes

1. int rowsAffected = command.ExecuteNonQuery();

• Meaning: Number of rows affected by DML statements (update/delete/insert)
• In this example: number of records matched & modified by update Article set title='111' where id=@id
• NOT the value returned by stored procedure return! They are completely independent

2. ReturnValue return‑value parameter

1. Parameter name must be the fixed string ReturnValue, custom names are not allowed
2. Direction = ParameterDirection.ReturnValue
3. SQL Server stored procedure return numeric_value only accepts int type
4. You can read this parameter’s value only after calling ExecuteNonQuery()

• return return value: only one value allowed, int type only
• output output parameter: multiple parameters allowed, supports many data types

Key Differences

-- Inside stored procedure
update Article set title='111' where id=4; -- Assume one row gets modified
return 10; -- return return‑valueCode language: PHP (php)

After execution:

• rowsAffected = 1 (rows affected by update)
• procReturnVal = 10 (value returned via return)

Summary

Method	Returned Content	Use‑case Scenarios
ExecuteReader	SqlDataReader (streaming multiple rows)	Fetch multiple rows of data
SqlDataAdapter.Fill	DataSet/DataTable (offline table)	Bind tables for WinForm controls
ExecuteScalar	object (first column of first row)	Retrieve single aggregate result
ExecuteNonQuery	int(affected rows) + ReturnValue return‑value	Insert / Update / Delete, need stored procedure status code

Stored Procedure Return