Answered by:

Should I ever use inline sql in an application?

Question

I've always taken the approach of creating a stored procedure whenever I'm dealing with a database. But sometimes I wonder if it's overkill to create a stored procedure when the query is a simple SELECT of a table.

So my question is: should I always create a stored procedure when I need to communicate with a database or are there exceptions to using inline sql?

Answers

Yes it makes sense. It give a layer of insulation between the application and the physical schema of the database.

It allows the DBA or Database Developer to modify the database for any reason, as long as the stored proc returns the same values. Otherwise, you would need to change your application and redistribute it when the database schema changes.

The advantages of the stored procedure are great, you can make a modification to the stored procedure and not worry about re-compiling your code. Also the stored procedure is compiled in SQL and results in less CPU hits to the server, since the execution
plan is already done.

All replies

The advantages of the stored procedure are great, you can make a modification to the stored procedure and not worry about re-compiling your code. Also the stored procedure is compiled in SQL and results in less CPU hits to the server, since the execution
plan is already done.

Yes it makes sense. It give a layer of insulation between the application and the physical schema of the database.

It allows the DBA or Database Developer to modify the database for any reason, as long as the stored proc returns the same values. Otherwise, you would need to change your application and redistribute it when the database schema changes.

Inline SQL is not a preferred option and should only be used if nothing else is available. Not sure what the front end tool you are using to build your application. If you are using .NET then you can use the Entity framework to avoid inline SQL as well
as writing stored procedures for simple DMLs.

The goal of a stored procedure is to be one statement with zero or more parameters. This applies any repeated action, so one shot SQL is done in-line instead. This is a gross generalization, of course.

I believe it makes sense. Your example stored proc accepts a parameter and returns a single column as a data set. Putting this single SELECT in a stored proc helps in reuse of the execution plan, insulates the application from the schema changes, centralizes
the access to the table thus making maintenance easier, helps in managing security ... and more benefits.

There are many people who blindly says "use stored procedures" with arguments that do not always hold. My take is that, yes, you can use inline SQL in your application, and with a proper usage - parameterised SQL statements, always using two-part notations
- your application design can be as sound as if you use stored procedures. But I also think that you should be consistent. There is a certain advantage if the application does not have stored procedures at all, because it makes deployment simpler. But if you
mix stored procedures and inline, you make things more difficult. Maybe the most important point is traceability. That is, the question "where is this table/column used?" This is always easier to answer if you only use stored procedures, but with good isolation
in client code it can work decently.

So my answer is that if you use stored procedures in your application already, stick to that. The only execption are administrative batches like "IF @@trancount > ROLLBACK TRANSACTION" or SET statements when you connect.

So my question is: should I always create a stored procedure when I need to communicate with a database or are there exceptions to using inline sql?

It depends. If security is paramount, such as with database containing NPI data, I suggest no in-line SQL and grant execute permissions only on stored procedures. OTOH, stored procedures provide little value if all queries are trivial and
the schema never changes. Parameterized inline SQL is a viable option in such cases.