Stored Procedures & Multiple Results

Level: Intermediate

ℹ️ Where This Fits

Stored procedures encapsulate logic, and QueryMultiple handles complex multi-result queries. These advanced patterns improve API efficiency.

ℹ️ What You'll Learn

• CommandType.StoredProcedure: Execute stored procedure
• Stored procedure benefits: Compiled on server, less network traffic
• Parameter passing: Input and output parameters
• QueryMultiple: Execute multiple queries, get all results
• GridReader: Access multiple result sets sequentially
• School Management: sp_CreateStudent, sp_GetStudentFeeReport, sp_UpdateAttendance
• Common mistakes: Wrong CommandType, not handling multiple result sets

Basic Stored Procedure

var student = conn.QuerySingleOrDefault<Student>(
    "sp_GetStudent",
    new { studentId = 101 },
    commandType: CommandType.StoredProcedure
);

Stored Procedure with Output Parameter

var parameters = new DynamicParameters();
parameters.Add("@name", "Ravi Kumar");
parameters.Add("@className", "10-A");
parameters.Add("@newId", dbType: DbType.Int32, direction: ParameterDirection.Output);

conn.Execute(
    "sp_CreateStudent",
    parameters,
    commandType: CommandType.StoredProcedure
);

var newStudentId = parameters.Get<int>("@newId");
return newStudentId;

QueryMultiple - Multiple Result Sets

var sql = @"
    SELECT Id, Name FROM Students WHERE ClassName = @className;
    SELECT Id, ExamName FROM Exams WHERE Status = 'Active';
    SELECT COUNT(*) as TotalCount FROM Students;";

using (var multi = conn.QueryMultiple(sql, new { className = "10-A" }))
{
    var students = multi.Read<Student>().ToList();
    var exams = multi.Read<Exam>().ToList();
    var count = multi.Read<int>().First();
}

Stored Procedure Returning Multiple Result Sets

var parameters = new DynamicParameters();
parameters.Add("@className", "10-A");

using (var multi = conn.QueryMultiple(
    "sp_GetStudentFeeReport",
    parameters,
    commandType: CommandType.StoredProcedure))
{
    var students = multi.Read<Student>().ToList();
    var feeAccounts = multi.Read<FeeAccount>().ToList();
    var summary = multi.Read<dynamic>().FirstOrDefault();
}

Example Stored Procedure (T-SQL)

CREATE PROCEDURE sp_GetStudentsByClass
    @className NVARCHAR(10)
AS
BEGIN
    SELECT Id, Name, RollNumber, Status FROM Students 
    WHERE ClassName = @className 
    ORDER BY Name;
end;

Best Practices

✓ Use stored procedures for complex business logic ✓ Check CommandType parameter ✓ Use output parameters for return values ✓ Handle multiple result sets with GridReader

💡 When to Use Stored Procedures

Good candidates:

• Complex multi-step operations
• Business rules enforcement
• Performance-critical queries
• Legacy database integration

🤖Use AI to Learn Faster

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

• "How do you call a stored procedure in Dapper?"
• "What's QueryMultiple used for?"
• "How do you get output parameters from a stored procedure?"
• "When should you use stored procedures vs raw SQL?"

💡 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

-> 05. Transactions & Batch Operations