Why do folks not use stored procedures in websites that access databases?

In SQL systems that support stored procedures, if you don't use certain functions, the sproc will often be stored on the server in compiled and optimized form. When the SQL text is embedded/constructed as a string in PHP or other web page, the text has to be sent to the DB and compiled before execution. This is not only less efficient in the database, it makes the PHP (or whatever) interpreter do more work before sending, and the web server more work to fetch the larger page. So I am wondering why I have never seen stored procedures used in any of the several large open source web apps I've looked at, not even some that ONLY support mySQL or MS SQL Server (both of which have stored procedures).

WGroleau

Posted 2016-10-05T18:44:42.617

Reputation: 438

1There are postives and negatives to using a stored procedure, the folks that don't use them, either have a specific reason not to use them or believe their negatives outweigh their postives. I have a few dozen reasons I don't use store procedures in my own projects that have database backend. – Ramhound – 2016-10-05T18:58:52.517

2Adding to @Ramhound's answer, if you ask 3 developers you'll get 4 answers, but some reasons are more common: using stored procs means placing business logic in the database and it's harder to manage stored procs under version control with the rest of the code assets in the project. – Atzmon – 2016-10-05T19:07:08.847

“When the SQL text is embedded/constructed as a string in PHP” and how would that not be necessary when using a stored procedure? The same parameters are still required. You still need an SQL command to call the SP. The amount of data will of course also be exactly the same. You can’t just magically make either complexity or data vanish. – Daniel B – 2016-10-05T20:28:56.063

This "debate" has been raging forever, and will never stop. ;) Read this Coding Horror entry from 2005: Stored Procedures vs. Ad-Hoc SQL for some good info so you can make your own decision. :)

– Ƭᴇcʜιᴇ007 – 2016-10-05T20:31:10.833

@DanielB: if you use a stored procedure, you need pass ONLY the parameters. The other way, you have to assemble the entire SQL, get the syntax right, prevent SQL injection, and then the DB server has to compile and optimize. – WGroleau – 2016-10-06T01:30:17.487

@WGroleau On a proper prepared statement, you also only “pass” the parameters. The query is static. SQL injection is also impossible. The server may cache the prepared statement. SPs for that purpose are really just like extracting a method. I get why you would use SPs when end users have direct database access. But I don’t get why anyone would allow that. – Daniel B – 2016-10-06T06:27:51.453

Of course I don't allow the users to write their own sprocs, any more than I would allow them to modify my PHP file! And there is no point caching a statement on the assumption that the next user is going to make the same entries. – WGroleau – 2016-10-06T12:56:37.773

Answers

Stored procedures lock you in a to specific DB platform. For example code written for MySQL will not run on SQL Server right away. That makes your application less portable. Of course there is more to the story: achieving portability would also require using standard data types only and not relying on platform-specific functions. Which in practice is not always the case.

Anonymous

Posted 2016-10-05T18:44:42.617

Reputation: 216

To the best of my.knowledge, my SQLwasportable，although I was never allowed to try it on any other platform. But I never saw any of the disadvantages alleged in that "coding horror" article. Also, it's much easier to write and test SQL in the database than in little pieces to be concatenated by some other language. And once written, it's much easier to insert a call into the PHP or ASPX than to work the entire SQL into it. – WGroleau – 2016-10-06T01:37:31.353

If the apps support multiple DB, it's imaginable that they would write store procedures for each DB they support.

Now, I don't know for SQL Server but regarding MySQL, many shared hostings don't allow creation of stored procedure at all, and some other features.

TTT

Posted 2016-10-05T18:44:42.617

Reputation: 175

I didn't say supporting multiple DBs. I said supporting only MySQL OR MSSQL. – WGroleau – 2016-10-06T01:27:28.440

I right, sorry,you didn't say exactly that, though their not excluded by you sentence either. I remove that wrong mention from my answer. – TTT – 2016-10-06T07:45:48.780

But those two do, and I have never seen sprocs used in code I didn't write that used them even when it clearly stated one of those was required. Since I have yet to be offered a credible reason otherwise, I wouldn't use a server that did not have stored procedures. – WGroleau – 2016-10-06T13:01:48.847