Interview question on stored procedures

Question

What kind of things would you not store in stored procedures?

Answer 1

Select ^ From Table

Edit

Okay that was a mildly vapid and tongue in check answer.

Things that you should have in Stored Procedures:

I personally don't store very simple queries inside of stored procedures, unless that query is called a lot. For example if I'm calling a database version check at the start of a program, and I'm only returning a single point of data, in a statement like Select version from DataVersion. That's probably not something that you should be calling from a stored procedure. It adds a small layer of obfuscation to the code if you have a SP to simply just get the a single point of data. That isn't to say that if it is a simple query it shouldn't be inside of a stored procedure.

One off queries, mostly not useful to have in a stored procedure. Why would you want to store something that you are only going to use once.

Queries that are very dynamic that do not work well being in a stored procedure. I've spent a lot of time looking through complex Stored Procedures that could be simplified a lot if they were moved into the program itself. This is very true with modern languages that support a better way of developing dynamic queries.

Answer 2

Stored procedures do not themselves store data.

Answer 3

Stored procedures are "defined once, used many times." If any changes are necessary, the (one and only one) definition of the stored procedure must be replaced. Dynamic SQL, of course, allows any SQL query to be issued at any time. Any change to a stored procedure instantly impacts every other piece of software, report, etc. (inside or outside of the DBMS) which directly or indirectly refers to it. It is not always possible to determine with certainty exactly what those impacts will be, nor what changes can safely be made without adversely impacting something else.

http://en.wikipedia.org/wiki/Stored_procedure

Answer 4

I think this is yet another case where the answer is: "it depends". First of all, the real question to answer might be: "How much business logic am I planning/willing to shift to the database?".

This is because you could practically have all your business logic in the database, within stored procedures (sprocs), where your sprocs would typically have a close one-to-one mapping with your application's UI or API. In this scenario you would probably have sprocs with names such as LoginUser, ValidateSession, CommitPurchase, etc.

On the other hand, you could have your sprocs acting as a thin data access/manipulation layer, where the real business logic sits somewhere else in your application framework, and the sprocs would simply be a set of glorified CRUD operators. In such a scenario, your sproc names would probably look more like GetListOfUsers, GetTop100Users, InsertUser, etc.

There are various advantages and disadvantages for the two approaches, and the feasibility of one approach or the other depends a lot on the particular context. The following are a few related Stack Overflow posts on this topic:

• Business Logic: Database or Application Layer
• Logic: Database or Application/2 (constraints check)
• Business Logic in Database versus Code?

Now as to your actual question, if your business logic will sit in the database, there is probably nothing that shouldn't be (or can't be) in a stored procedure. On the other hand, for CRUD-like sprocs, I think there should be nothing in them unless what is strictly necessary to ensure that the data is correct and consistent.

Answer 5

This isn't a question about whether to use stored procedures. It's a hypothetical interview question. The interviewer wants to be confident that you understand SPs aren't a dumping ground for code and data that are better suited elsewhere in the system.

A reasonable answer might include the following:

• Data such as constants or client-specific logic never belong in SPs.
• Passwords or other security tokens never belong in SPs or any code.
• Complex processing may not be suited for today's rather primitive SP languages.
• Business logic may be better suited for the middle-tier rather than in SPs, due to differences in the expressive nature of the languages, maintainability, OO design, or other factors.
• Presentation layer code is probably best suited for the UI, not SPs.

With interview questions such as this one, the goal is not to determine the one true correct or incorrect response. Instead, the interviewer has his ideal response in mind, and your job is to identify that response. If your interviewer is a DBA, manages a DBA group, or appears to align his or herself strongly with a DB-focused methodology, then you may wish to play down the weakness of SP languages, and skip the recommendation to avoid complicated business logic in the database. There is a time and place for those debates, but not during your interview!