What is a stored procedure?

Defined:
To use the usual or very complex work, which is written in a SQL statement and stored with a specified name, you can automatically complete the command by simply calling execute when you want the database to provide the same functionality as the defined stored procedure.
Here, someone might ask: so the stored procedure is just a bunch of SQL statements.
Why do Microsoft companies add this technology?
So what's the difference between a stored procedure and a normal SQL statement?

Advantages of stored procedures:
1. Stored procedures are compiled only when created, and each time a stored procedure is executed without recompiling, and the general SQL statements are compiled every time, so using stored procedures can improve database execution speed.
2. When complex operations are performed on the database (for example, when multiple tables are update,insert,query,delete), this complex operation can be encapsulated in a stored procedure and used in conjunction with transaction processing provided by the database.
3. Stored procedures can be reused to reduce the workload of database developers
4. High security, can be set only a certain user to have the right to use the specified stored procedures
types of stored procedures:
1. System stored procedures: to sp_ the beginning of the system for the various settings. Get information. Related Management work,
such as sp_help is to obtain the relevant information of the specified object
2. Extended stored procedures begin with XP_ to invoke the functionality provided by the operating system
EXEC master.. xp_cmdshell ' Ping 10.8.16.1 '
3. User-defined stored procedures, which we refer to as stored procedures
Common format
Create procedure Procedue_name
[@parameter Data_type] [Output]
[With] {Recompile|encryption}
As
Sql_statement
Explain:
Output: Indicates that this parameter is a can be returned
with {recompile|encryption}
Recompile: means recompiling every time this stored procedure is executed
Encryption: The contents of the stored procedure created are encrypted
Such as:
The contents of the table book are as follows
Numbered title price
001 C Language Introduction $
002 PowerBuilder Report Development $52
Instance 1: Stored procedures for querying the contents of a table book
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 this table
Create proc Insert_book
@param1 Char, @param2 varchar, @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 of execution:
DECLARE @total_price Money
EXEC insert_book ' 003 ', ' Delphi Control Development Guide ', $, @total_price
print ' Total amount is ' +convert (varchar, @total_price)
Go
3 kinds of return values for stored procedures:
1. Returns an integer with return
2. Returns parameters in output format
3.Recordset
The difference between return values:
Both output and return can be received in a batch program and the recordset is returned to the client of the execution batch
Example 3: With two tables for product,order, the table reads as follows:
Product
Product number Product name Customer order number
001 Pen 30
002 Brush 50
003 Pencil 100
Order
Product number Customer Name Customer deposit
001 Nanshan District, $
002 Luohu District $
003 Baoan District $
Implement a numbered join condition that joins two tables into a temporary table containing only numbers. Product name. Customer Name. Deposit. Total amount,
Total Amount = deposit * number, temporary table placed in stored procedure
The code is as follows:
Create proc Temp_sale
As
Select a. Product number, a. Product name, B. Customer's name, B. Customer's deposit, A. Customer order * B. Customer deposit as total amount
into #temptable from product a INNER join order B on a. Product number =b. Product number
If @ @error =0
print ' good '
Else
print ' Fail '
Go

--------------------------------------------------------------------------------------------------------------- -----

Introduction to Stored Procedures

First, introduce what is stored procedure
A stored procedure is a program that is written using the Tranact-sql language provided by SQL Server. The Tranact-sql language is the language that SQL Server provides specifically for designing database applications, which is the primary programming interface between applications and SQL Server databases. It is like the Pro-sql in an Oracle database system and the INFORMIX-4GL language available in the database system of Informix. This type of language provides the following features, allowing users to design a program that meets the needs of the reference:
1), Variable description
2), ANSI-compatible SQL commands (such as select,update ...)
3), General Process Control command (If...else ..., while ...)
4), internal function

Second, the writing format of the stored procedure

CREATE PROCEDURE [owner.] stored procedure name [; program number]
[Parameter # #,... Parameter #1024)]
[With
{RECOMPILE |   Encryption | RECOMPILE, encryption}
]
[For REPLICATION]
As Program line

The stored procedure name cannot be more than 128 words. Set up to 1024 parameters per stored procedure
(Version above SQL Server 7.0), the parameters are used as follows:

@ Parameter name data type [varying] [= default] [OUTPUT]

Each parameter name must have an "@" symbol, and the parameters for each stored procedure are used internally by the program only, and the type of the parameter can be used in addition to image for other SQL Server-supported data types.
[= default] is the same as when we set up the database to set a field defaults, here is to set the default value for this parameter. [Output] is used to specify that the parameter is both an input and an output value. That is, when the stored procedure is invoked, if the specified parameter value is the parameter we need to enter, and also needs to be output in the result, the item must be in output, and if it is only for output parameters, you can use the cursor , and you must specify both the varying and output statements when using this parameter.

Example:
CREATE PROCEDURE order_tot_amt @o_id int, @p_tot int output as
SELECT @p_tot = SUM (unitprice*quantity)
From OrderDetails
WHERE ordered= @o_id

Example Description:
The example is to establish a simple stored procedure Order_tot_amt, which calculates the total sales of the order (OrderDetails) according to the Order ID number (@o_id) entered by the user, [unit Price (UnitPrice) * Quantity ( Quantity)], this amount is output by @p_tot This parameter to the program that invokes the stored procedure

iii. executing stored procedures in SQL Server

In 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 is to execute order_tot_amt this stored procedure, to calculate the order number 1 of the order sales amount, we define @tot_amt as the output parameter, to accept the result we want