Can you explain what a stored procedure in SQL is and how it is used?

A stored procedure is a pre-compiled set of SQL commands saved in the database under a specific name. It allows performing complex data operations on the database server.

Advantages of using stored procedures:

• Improves performance by reducing the amount of data transferred and enabling code reuse.
• Ensures security, as access to data can be restricted by calling procedures.
• Simplifies maintenance and modification of business logic without changing client code.

Example of calling a stored procedure from C#:

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("ProcedureName", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@param", value);
    conn.Open();
    cmd.ExecuteNonQuery();
}