sqlserver存储过程（SQL server stored procedures）.doc

sqlserver存储过程（SQL server stored procedures） SQL server stored procedures Definition: Will commonly used or very complicated work, use SQL statements written in advance with a specified name stored, so that database and has the function of the stored procedure defined in the same service, just call the execute, can automatically complete the command. Now, when we get to this point, someone might want to say, well, the stored procedure is just a bunch of SQL statements, right? Why did Microsoft add the technology? So whats the difference between a stored procedure and a generic SQL statement? Advantages of stored procedures: Stored procedure when creating only compile, execute the stored procedure every time after dont need to recompile, and general SQL statements each perform a compile time, so use the stored procedure execution speed can improve the database. 2. When the complex operation of database (such as multiple tables for the Update, Insert, Query, Delete), the complex operation using stored procedures can be encapsulated in combination with database provided by the transaction. 3. Stored procedures can be reused to reduce the workload of database developers Security is high, but only one user has the right to use the specified stored procedure Types of stored procedures: 1. System stored procedures: start with sp_, and use to carry out various Settings of the system. Obtain information. For example, sp_help is the information to get the specified object 2. The extended stored procedure starts with XP_ to invoke the functionality provided by the operating system The exec master.. Arbitrary ping 3. User-defined stored procedures, which we refer to as stored procedures Commonly used formats Create procedure procedue_name [@ parameter data_type] [output] [with] {recompile | encryption} The as sql_statement Explanation: Output: indicates that this parameter is returned With {recompile | encryption} Recompile: indicates that it is recompiled every time this store