Stored Procedure Vs. Select Statement

[CherylD]

After having this discussion with a colleage I'm looking for more opinions. From a programming standpoint, why would on always use a stored procedure over a sql select statement. What are the pros and cons of each?

If the point of writing the procedure is to only select a field or two from the database and based on the results return alternate values (ie, if userallowed='Y' return 4). Why do that in a procedure when in a program you could also execute that SQL select statement and then you have the field values to then run your own if statements on. Why one over the other? Is it best to write procedures for ANY SQL statement so that SQL statements NEVER appear in code only procedures get written?

 

[sguslan]

For one, table security is enhanced. It is more secure to hard-code "some" sql functionality into a stored procedure and allow only execute privileges on it rather than allowing full scale select or even worse update/delete....

I quote some nice article on the issue found at the following url :

http://www.vbip.com/books/1861003064/chapter_3064_01.asp

"Stored procedures are more efficient than SQL statements, when executed from a program. The reason why this is true is that when the stored procedure is created and saved it is compiled. During the compilation process, SQL Server creates and optimizes an execution plan for the stored procedure. Also, once a stored procedure is executed, it is placed in SQL Server cache. This means that subsequent executions are executed from cache, which also provides improved performance.

SQL statements executed from your programs, however, are not as efficient. Each time the SQL statements are executed by your program, SQL Server must compile and optimize them before they actually get executed. While this performance decrease is not that noticeable on small amounts of SQL statements, or on your personal computer running the Desktop edition of SQL Server, it becomes more evident on large scale SQL Server systems with multiple databases and hundreds of concurrent clients."

 

[ColinM]

Stored Procedures are easier to mantain. It keeps all you SQL code in one place, separating out design logic from business/data logic.
Also if you have a front-end program (for example a form in VB) then if you need to alter something, like a table name or maybe extend functionality, it is easier to do this in the stored procedure as opposed to in your VB code. You would have to alter the VB code, recompile and redeploy for any change.