Stored Procedures

Level: Intermediate

ℹ️ Where This Fits

Stored procedures encapsulate SQL logic in the database. Use them for complex business operations, reducing network traffic and improving security by hiding query details from the client.

ℹ️ What You'll Learn

• Stored procedure purpose: SQL code stored in database (encapsulation, reuse, performance)
• Benefits: Network efficiency (less data), security (hide query), reusable, versioning
• CommandType.StoredProcedure: Execute stored procedure (not SQL query)
• Parameter passing: Input and output parameters
• Input parameters: Pass values to stored procedure (@name, @className)
• Output parameters: Get values back from stored procedure (direction = Output)
• SCOPE_IDENTITY(): Get last inserted ID from stored procedure
• Return value: Stored procedures return single integer (status code, count, etc)
• ParameterDirection.Input: Input value (default)
• ParameterDirection.Output: Get value from stored procedure
• ParameterDirection.ReturnValue: Procedure's return value
• Multiple operations: Call one stored procedure instead of multiple queries
• School Management examples: sp_CreateStudent, sp_UpdateFeeStatus, sp_GetStudentsByClass
• Error handling: Check return value for error codes (0=success, >0=error)
• T-SQL basics: CREATE PROCEDURE, BEGIN/END, SELECT, INSERT/UPDATE/DELETE
• Common mistakes: Wrong ParameterDirection, not calling Dispose, not checking return value

Basic Stored Procedure Call

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetStudentsByClass";
        cmd.Parameters.AddWithValue("@className", "10-A");

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var students = new List<Student>();
            while (await reader.ReadAsync())
            {
                students.Add(new Student
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"],
                    ClassName = (string)reader["ClassName"]
                });
            }
            return students;
        }
    }
}

Output Parameters

Capture values from stored procedure:

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_CreateStudent";
        
        cmd.Parameters.AddWithValue("@name", "Ravi Kumar");
        cmd.Parameters.AddWithValue("@className", "10-A");
        cmd.Parameters.AddWithValue("@status", "Active");
        
        // Output parameter to get new student ID
        var idParam = cmd.Parameters.Add("@newId", SqlDbType.Int);
        idParam.Direction = ParameterDirection.Output;
        
        await cmd.ExecuteNonQueryAsync();
        
        var newStudentId = (int)idParam.Value;  // Get ID generated by stored procedure
        return newStudentId;
    }
}

Return Value from Stored Procedure

using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_UpdateFeeStatus";
    
    cmd.Parameters.AddWithValue("@studentId", 101);
    cmd.Parameters.AddWithValue("@status", "Paid");
    cmd.Parameters.AddWithValue("@paidAmount", 50000);
    
    // Capture return value (0=success, 1=student not found, 2=error)
    var returnParam = cmd.Parameters.Add("@returnValue", SqlDbType.Int);
    returnParam.Direction = ParameterDirection.ReturnValue;
    
    await cmd.ExecuteNonQueryAsync();
    
    int returnValue = (int)returnParam.Value;
    
    if (returnValue == 0)
        return true;  // Success
    else if (returnValue == 1)
        throw new Exception("Student not found");
    else
        throw new Exception("Database error");
}

Example Stored Procedure (T-SQL)

CREATE PROCEDURE sp_CreateStudent
    @name NVARCHAR(100),
    @className NVARCHAR(10),
    @status NVARCHAR(20),
    @newId INT OUTPUT
AS
BEGIN
    INSERT INTO Students (Name, ClassName, Status, CreatedAt)
    VALUES (@name, @className, @status, GETDATE());
    
    SET @newId = SCOPE_IDENTITY();  -- Get inserted ID
    
    RETURN 0;  -- Success
END;

Complex Stored Procedure Example

CREATE PROCEDURE sp_GetStudentFeeReport
    @className NVARCHAR(10),
    @totalCount INT OUTPUT,
    @totalPending DECIMAL OUTPUT
AS
BEGIN
    -- Get total students
    SELECT @totalCount = COUNT(*) 
    FROM Students 
    WHERE ClassName = @className AND Status = 'Active';
    
    -- Get total pending fees
    SELECT @totalPending = SUM(TotalFees - PaidAmount)
    FROM FeeAccount fa
    JOIN Students s ON fa.StudentId = s.Id
    WHERE s.ClassName = @className AND fa.Status = 'Pending';
    
    RETURN 0;
END;

Call Complex Procedure

using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_GetStudentFeeReport";
    cmd.Parameters.AddWithValue("@className", "10-A");
    
    var countParam = cmd.Parameters.Add("@totalCount", SqlDbType.Int);
    countParam.Direction = ParameterDirection.Output;
    
    var pendingParam = cmd.Parameters.Add("@totalPending", SqlDbType.Decimal);
    pendingParam.Direction = ParameterDirection.Output;
    
    await cmd.ExecuteNonQueryAsync();
    
    int count = (int)countParam.Value;
    decimal pending = (decimal)pendingParam.Value;
    
    return new FeeReport { StudentCount = count, PendingAmount = pending };
}

Best Practices

✓ Use output parameters for values ✓ Use return value for status codes ✓ Keep procedures focused (single responsibility) ✓ Always dispose command/reader ✓ Document procedure parameters

💡 Network Efficiency

Calling 5 queries from C#:

• 5 round trips to database
• More latency

One stored procedure with same logic:

• 1 round trip (procedure compiled, optimized on server)
• Less latency, more efficient

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Stored Procedures. Try these prompts:

• "What's the difference between input and output parameters?"
• "How do you get the last inserted ID from a stored procedure?"
• "When should you use stored procedures vs direct queries?"
• "What does CommandType.StoredProcedure do?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> EF Core 01. Entity Framework Core Fundamentals