DenkzeitWiki

Stored Procedures

A stored procedure (sometimes called a sproc) is a program (or procedure) which is physically stored within a database. The exact implementation of a stored procedure varies from one database to another. In most cases however, stored procedures allow for an API to be defined for a database, rather than having a client application interact with the tables and other database objects directly.

• http://en.wikipedia.org/wiki/Stored_procedure
  
• Writing CLR Stored Procedures in C# - Introduction to C# (Part 1)

• Stored procedures are bad, m'kay?
  • nough said...
  
  
• Again, other author: Stored procedures are bad, m'kay?
  • Cons
    • SQL server caches the SQL code as the index and the data being that of the compiled version of the SQL code. Stored procedures will use this cache because the actual SQL code doesn't change. Keeping that in mind, if you parameterize your generated SQL code, you are in effect creating a "stored procedure" because the actual SQL does not change. That directly implies that SQL Server caches the compiled version of each SQL and _RE-USES_ it because the actual SQL does not change if you used parameterized queries.
    • The _only_ performance upgrade that you will get with Stored procedures is the network will be lower.
  • Pros
    • How many more round trips are you making to the DB now that you're accessing it through entity objects rather than say, a service api that maps to some bizlogic requirement?
  • I can change the way the SQL executes without having to release a new client application or recompile my business objects
  
  
• The Pragmatic Adhoc SQL vs Stored Procedures Discussion

• http://www.kevinjensen.com/2006/09/05/the-dynamic-sql-vs-stored-procedures-is-the-most-common-geek-battle-of-the-century/
• Who needs Stored Procedures, anyways?
  
  
• Why Parameterized Queries Are Not Secure
  • unexperienced developers can still spoil security if Parameterized Queries are incorrectly used - this cannot happen with StoredProcedures.