Enterprise Stored Procedures

Stored Procedures (Procs) are the bridge between your C# code and the database. While some developers prefer raw SQL in EF Core, Stored Procedures provide superior security, network efficiency, and performance tuning capabilities.

1. Why use Procs?

• Security: You can grant a user permission to EXECUTE a proc without giving them access to the underlying tables. This prevents bulk data theft.
• Network Traffic: Instead of sending a 500-line SQL string over the wire, you just send EXEC GetReport 5.
• Pre-Compiled: SQL Server caches the execution plan for procs, making them slightly faster for complex logic.

2. Output Parameters

Don't return a whole table if you only need one value. Use OUTPUT parameters to return specific values (like a newly created Identity ID) back to your C# app efficiently.

4. Interview Mastery

Q: "Should I put complex business logic inside Stored Procedures?"

Architect Answer: "It depends on the **Logic Type**. Data-centric logic (mass updates, complex aggregations) belongs in a Proc because it is faster to do it where the data lives. UI-centric or external logic (sending emails, calling APIs) belongs in the C# code. Putting too much business logic in Procs creates a 'Black Box' that is hard to version-control and unit-test."