Benefits of using Stored Procedures

-- 10.1 Stored Procedure BASICS (p273) (Excerpted from SQL Server 2008 practice \ Chapter 10th _ Stored Procedure (p273 page)
-- over the past few years, I have formed a strong preference for using stored procedure whenever possible. Based on my experience, there are many advantages to using stored procedures, but there is no harm. Generally,
-- Reasons for objection to using Stored Procedures come from Applications developers, they prefer to use ad hoc SQL at the application layer, and have not been trained to use stored procedures. Together with independent applications and database administrators,
-- stored procedures also face the developer to the database administrator on the T-SQL Code out of control. Assume that your database management team is strong and willing to provide timely help for transferring stored procedures,
-- the advantages of using stored procedures are much greater than the loss of control.

-- using stored procedures has the following benefits.
-- * 1) stored procedures help aggregate T-SQL code at the database layer. Websites or applications that embed ad hoc SQL statements are difficult to modify in the application environment. When ad hoc SQL statements are embedded in the application, you may spend too much time trying to find and debug embedded SQL statements.
-- once a bug is found, you may need to re-compile the executable program, causing unnecessary application deployment to temporarily stop or suffer. If you concentrate your T-SQL on stored procedures,
-- you just need to concentrate on one place to query SQL code or SQL batch processing. If you can correctly document the code and standardize the code, the stored procedure will improve the availability of the entire application.
-- * 2) stored procedures help large ad hoc queries reduce network traffic. Writing application calls instead of 500 rows of SQL calls to execute stored procedures has a positive impact on the network and application performance, especially when the calls are repeated several thousand times in one minute.
-- * 3) stored procedures promote code availability. For example, if your website application uses a drop-down menu to contain a group of cities, and this drop-down menu is used for many webpages,
-- you can call the stored procedure on each page instead of embedding the same SQL statement in multiple places.
-- * 4) the Stored Procedure fades out the data acquisition method. If you modify the basic table that provides the source data, the stored procedure (similar to the view) can make the application transparent to the modification. In this way, you do not need to modify the underlying code of the application.
-- you can replace the old table with a new one, and the application will not be informed if the same columns and data types are returned to the application.
-- * 5) different from a view, a stored procedure can use stream control technology, temporary tables, and table variables.
-- * 6) the impact of stored procedures on query response time is relatively stable. If you use a large number of ad hoc queries, you may notice that sometimes the time required to return results from the query changes greatly.
-- This may be caused by external factors such as concurrent table (LOCK) activities or resource problems (memory, CPU. On the other hand, ad hoc queries may fail to be executed because SQL Server sometimes chooses execution plans with lower efficiency.
-- stored procedures provide more reliable query plan cache, so they can be reused. Note that here I use the word "reliable" instead of "quick ". Ad hoc queries sometimes perform better than stored procedures.
-- however, they fully depend on the cached environment of the execution plan (the "detected" parameter ), and how you test, tune, and implement the Code.

-- If the preceding reasons cannot convince you that the stored procedure is very good, let's take a look at the security advantages. Direct access to the SQL server instance and its database tables (worse case, access to SysAdmin) can cause security risks.
-- Inline ad hoc code is particularly vulnerable to SQL injection attacks. If a harmful T-SQL is inserted into the T-SQL code of an existing application before the T-SQL is sent to the SQL server instance, SQL Injection occurs. Besides SQL injection attacks,
-- If some people get Inline code, they can collect information about the infrastructure in the database to guide them to attack. Make sure that all SQL statements in the stored procedure are referenced by the application only-not every column and table name.

-- Another security benefit of stored procedures is that database users and/or database roles can be granted access to them rather than direct access to tables. The stored procedure can be used as the control layer,
-- You can select which columns and rows can be modified by stored procedures (and callers) and which cannot.