What is a stored procedure?

Definition:
Store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.
At this point, someone may ask: is the stored procedure just a bunch of SQL statements?
Why does Microsoft add this technology?
What is the difference between a stored procedure and a general SQL statement?
Advantages of stored procedures:
1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.
2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.
3. The stored procedure can be reused to reduce the workload of database developers.
4. High security. You can set that only one user has the right to use the specified stored procedure.
Types of stored procedures:
1. system stored procedure: starts with SP _. It is used to set the system, obtain information, and manage the system,
For example, sp_help is used to obtain information about the specified object.
2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.
Exec master .. xp_mongoshell 'Ping 10.8.16.1'
3. User-Defined stored procedures, which we refer to as stored procedures
Common formats
Create procedure procedue_name
[@ Parameter data_type] [Output]
[With] {recompile | encryption}
As
SQL _statement
Explanation:
Output: indicates that this parameter can be returned.
With {recompile | encryption}
Recompile: Indicates re-compiling every time this stored procedure is executed.
Encryption: the content of the created stored procedure is encrypted.
For example:
The table book content is as follows:
Title price
001 C language entry $30
002 PowerBuilder report development $52
Example 1: query the stored procedure of the table Book content
Create proc query_book
As
Select * from book
Go
Exec query_book
Example 2: Add a record to the table book and query the total amount of all books in the table
Create proc insert_book
@ Param1 char (10), @ param2 varchar (20), @ param3 money, @ param4 money output
With encryption --------- encryption
As
Insert book (number, title, price) Values (@ param1, @ param2, @ param3)
Select @ param4 = sum (price) from book
Go
Example:
Declare @ total_price money
Exec insert_book '003 ', 'delphi control development Guide', $100, @ total_price
Print 'total amount is '+ convert (varchar, @ total_price)
Go
Three types of Stored Procedure return values:
1. Return an integer with Return
2. Return parameters in output format
3. Recordset
Differences between return values:
Both output and return can be received using variables in a batch program, while recordset is passed back to the client that executes the batch.
Example 3: There are two tables: Product and Order. The table content is as follows:
Product
Product NO. Product Name customer order quantity
001 pen 30
002 brush 50
003 pencil 100
Order
Product NO. Customer name customer deposit
001 Nanshan District $30
002 Luohu district $50
003 Baoan District $4
Connect two tables into a temporary table by serial number. This table only contains serial numbers, product names, Customer names, deposits, and total amount,
Total amount = Deposit * Number of orders. Temporary tables are stored in the stored procedure.
The Code is as follows:
Create proc temp_sale
As
Select a. Product NO., a. Product Name, B. Customer name, B. customer deposit, a. Number of customer orders * B. Total amount of customer deposits
Into # temptable from Product a inner join Order B on a. Product NO. = B. Product NO.
If @ error = 0
Print 'good'
Else
& N bsp; print 'fail'
Go

Stored Procedure
I. first introduce what is a stored procedure
Stored procedures are programs written using the Tranact-SQL language provided by SQL Server. Tranact-SQL is a language provided by SQL Server for designing database applications. It is the main programming interface between applications and SQL Server databases. It is similar to the Informix-4GL language in the Oracle database system's Pro-SQL and Informix database systems. This type of language mainly provides the following functions, allowing users to design a program that meets the reference requirements:
1) variable description
2) ANSI compatible SQL commands (such as Select, Update ....)
3) general process control commands (if... Else... , While ....)
4) internal functions

Ii. Writing format of the stored procedure

Create procedure [owner.] stored PROCEDURE name [; program id]
[(Parameter #1 ,... Parameter #1024)]
[
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[For replication]
AS program line

The name of the stored procedure cannot exceed 128 characters. A maximum of 1024 parameters can be set in each stored procedure.
(SQL Server 7.0 or later), the parameter usage is as follows:

@ Parameter Name Data Type [VARYING] [= internal value] [OUTPUT]

Each parameter name must have a "@" symbol before it. The parameters of each stored procedure are only used internally by the program. The parameter types include IMAGE, data Types supported by other SQL servers can be used.
[= Internal value] is equivalent to setting the default value of a field when creating a database. Here we set the default value for this parameter. [OUTPUT] is used to specify that this parameter has both input and OUTPUT values. That is, when the stored procedure is called, if the specified parameter value is the parameter we need to enter, if the parameter must be OUTPUT in the result, it must be OUTPUT. If the parameter is used only for OUTPUT, you can use CURSOR, the VARYING and OUTPUT statements must be specified.

Example:
Create procedure order_tot_amt @ o_id int, @ p_tot int output
SELECT @ p_tot = sum (Unitprice * Quantity)
FROM orderdetails
WHERE ordered = @ o_id

Example:
In this example, a simple Stored Procedure order_tot_amt is created. This stored procedure is based on the order ID number (@ o_id) entered by the user, by the Order List (orderdetails) calculates the total sales of this Order [unit price * Quantity (Quantity)]. This amount is output to the program that calls this stored procedure through @ p_tot.

3. Execute the stored procedure in SQL Server

In the SQL Server Query analyzer, enter the following code:
Declare @ tot_amt int
Execute order_tot_amt 1, @ tot_amt output
Select @ tot_amt

The above code executes the order_tot_amt stored procedure and calculates the order sales amount with Order Number 1. We define @ tot_amt as the output parameter to undertake the results we want.

Compile the SQL statement before executing it. Stored procedures are compiled SQL statements. It can be used directly.

A stored procedure is a process written by flow control and SQL statements. After compilation and optimization, the stored procedure is stored on the database server and can be called as long as it is used. In ORACLE, several associated processes can be combined to form a package.
Using Stored Procedures has the following advantages:
* The Stored Procedure capability greatly enhances the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With high flexibility, they can complete complicated judgment and computation.
* Ensures data security and integrity.
# Using Stored Procedures, users without permissions can indirectly access the database under control to ensure data security.
# Through the stored procedure, relevant actions can be taken together to maintain the integrity of the database.
* Before the stored procedure is run, the database has analyzed its syntax and provided an optimization execution plan. This compiled process can greatly improve the performance of SQL statements. Since most of the SQL statement execution has been completed, the stored procedure can be executed at an extremely fast speed.
* Reduces network traffic.
* Put the computing program that embodies the enterprise rules into the database server:
# Centralized control.
# When enterprise rules change, you can change the stored procedure on the server without modifying any applications. Enterprise rules are characterized by frequent changes. If the computing program that reflects the enterprise rules is put into the application, when the enterprise rules change, it takes a lot of effort to modify the application (modifying, releasing, and installing the application ). If you put the operations that reflect the enterprise rules into the stored procedure, when the enterprise rules change, you only need to modify the stored procedure, and the application does not need to change.