Database Stored Procedures

By convention, we should first talk about its advantages: (1)Putting SQL code in a stored procedure has better performance, because SQL-server generates and caches the execution plan of the stored procedure during its first execution. (2)Using the stored procedure can better maintain the code for accessing and operating data. It is concentrated in one place, which makes the implementation of the three-tier architecture easier. (The stored procedure will form a data layer) (3)SQL Server allows you to set different security licenses for each stored procedure. (4)SQL queries created in the Code are more vulnerable to SQL injection attacks, which is a serious security threat. (5)This may be a personal hobby. Separating SQL from Code makes the code easier to manage and clearer: calling a stored procedure name is better than using a connection string to pass it to the database to create an SQL query. The following describes the syntax of a stored procedure: Create procedure As <Here is an SQL statement> For example, the SQL statement used to query the information of a store (such as household appliances and clothing in a large mall) Select distinct mentid, name, description from department Department is the name of the category, category mentid (primary key ID), name (category name), and description (Category Description) are fields. The stored procedure is: Create procedure getdocumments As Select distinct mentid, name, description From Department There are two parameters in the stored procedure: Encrurtion: disrupt the stored procedure, so that the content is not so easy to see. It does not encrypt data, but only protects the source code from being peeked and modified. Recompile: Because the database generates and caches the execution plan of the stored procedure when it executes the stored procedure for the first time, this parameter indicates that the scheduler is re-compiled every time the stored procedure is run. When these two parameters are added to the statement, use the with connection. For example, the preceding statement is modified and changed: Create procedure getdocumments With encrurtion, recompile As Select distinct mentid, name, description From Department We all know that SQL statements contain the where keyword. This keyword is followed by query conditions, which means that the data obtained from the database by some SQL statements depends on external parameters, obviously, the preceding statements are incomplete. The syntax for creating a stored procedure with parameters is as follows:             Create procedure <procedure name> [( <Parameter Name><Parameter type> [= <default value>][Input | output], <Parameter Name><Parameter type> [= <default value>][Input | output], ........... ........... )] As <Stored Procedure body> The options in square brackets are optional. The parameter is also optional, but to be specified, it must be placed in parentheses. For each parameter, at least the name and data type must be provided. You can provide a default value for the parameter. In this way, if the called function does not provide a value for the parameter, it will be replaced by the default value. You can also specify whether the parameter is an input parameter or an output parameter. By default, all parameters are input. The output parameter value is set in the stored procedure. After the stored procedure is executed, it is read by the calling function. The processing of stored procedure parameters is the same as that of other SQL statements. Their names start with @, for example, @ departmentid. The simplest syntax for assigning values to output parameters in a stored procedure is as follows: Select @ resolve mentid = 5 The following is a simple example of a stored procedure with parameters: In the preceding example, if you want to query the category based on the parameter mentid, you need to create a stored procedure with parameters: Create procedure getdepartmentdetails (@ Brief mentid INT) As Select name, description From Department Where required mentid = @ required mentid Now we will introduce how to add the getparts stored procedure to the database in vs2005. (^_^ No more) (1) make sure to expand in Database Explorer and select the data connection of the database. Select data --- add new ---- stored procedure. Alternatively, right-click the Stored Procedure node in Database Explorer and select Add new stored procedure. (2) Replace the default text with your getdocumments stored procedure: Create procedure getdocumments As Select distinct mentid, name, description From Department (3) press Ctrl + S to save the stored procedure. Different from the table, you are not required to specify a name because the database can know the name from the statement of the stored procedure. (4) Now, test the stored procedure you entered and see how it works. In Database Explorer, find the getdocumments Stored Procedure node and select execute. After the stored procedure is run, the execution result is displayed in the output window.