I've been ask this question in two different interviews...
What is the benefit of using stored proc ? another guy asked... when would you use a stored proc over inline sql
Stored proc makes an application database agnostic ... application layer is separate from the data
Stored procs are compiled
When do we want to use a SP over inline SQL? I didn't answer this one.
No matter how much I prepare for an interview... there's always something unexpected thrown at me!