DEV Community

loading...

Discussion on: When is it a good idea to use stored procedures?

Collapse
jfrankcarr profile image
Frank Carr

There's a lot of gray area beyond the obvious ones like bulk data loads and transformations. But basically you want the database server to do the things that it's been optimized to do, such as extracting subsets of data, sorting data and joining tables. The database engine has been written and tested to accomplish these tasks very well and the algorithms were written by a team of top notch programmers. In most cases, you shouldn't need to "reinvent the wheel" in your code.

On the opposite end, more complex business logic usually belongs in non-database code, such as in an API or service layer. One indicator of this is when you see a stored proc with a lot of cursors, loops, branches, string concats, calls out to multiple stored procs or functions and the like. This can and does work but it tends to create maintenance issues, especially when the original database savvy programmer leaves the company.