1. What is a stored procedure?
A stored procedure is one or more SQL commands that are stored as executable objects in the database.
In layman's terms: a stored procedure is actually a set of SQL statements that can perform certain operations.
2. Why use stored procedures?
1) The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures increases the speed of the database execution.
2) When complex operations are performed on a database, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional 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 some users have the right to use the specified stored procedure
3, the syntax of the stored procedure?
1) syntax for creating stored procedures
create proc Stored procedure name
@ DECLARE field data type
As
Begin
EXECUTE statement
End
Go
Such as:
Create proc Xp_stuproc
@name varchar (20)
As
Begin
SELECT * from Student where [email protected]
End
Go
2) Execute Stored procedure syntax
exec xp_stuproc ' Zhaoyuchu ' or
exec xp_stuproc @name = ' Zhaoyuchu '
3, how to use the stored procedure?
1) Create a database, create a data table, add table data, and more
2. View data table (used to create stored procedure)
3. Create a stored procedure without parameters
1) query information for all students (using stored procedures)
To create a non-parametric stored procedure stuproc:
The result of executing the stored procedure:
4. Create a stored procedure with parameters (external variable assignment, internal variable assignment, output parameter)
1) assigning values to external variables
To create a stored procedure with parameters Stuprocparm:
The result after the call:
2) Internal variable assignment
To create a stored procedure StuProcParm1:
The result after the call:
3) with output parameters
To create a stored procedure StuProcParm2:
The result after the call:
4) Declaring local variables
To create a stored procedure STUPROCPARM3:
The result after the call
5) Output Local variables
To create a stored procedure STUPROCPARM4:
The result after the call:
Ps: Refer to Learning Stored procedure address: http://www.cnblogs.com/jiajiayuan/archive/2011/06/15/2081201.html
Basic knowledge record of stored procedures in SQL Server