Basic knowledge of stored procedures

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.

Http://hovertree.com/h/bjaf/b05uxijr.htm

So 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 can increase 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 procedures

How does the stored procedure work?
The following table student is used to understand the stored procedure, because it is to understand the simple use of stored procedures, so all the examples are simple.

No parameter stored procedure:
Select all the information in the student table,

Create proc Stuproc
AS//here cannot be omitted without writing
Begin//begin and end are a pair, you cannot write only one, but you can never write
Select S#,sname,sage,ssex from Student
End
Go

There are parameter stored procedures:
Global variables
A global variable, also known as an external variable, is defined outside the function and is scoped to the end of the program file, starting at the definition of the variable.
Select student information for the specified name:

Create proc Stuproc
@sname varchar (100)
As
Begin
Select S#,sname,sage,ssex from student where [email protected]
End
Go
--Why Ask Hovertree.com
exec stuproc ' lei '//EXECUTE statement

The above is an external assignment to the variable, or you can set the default value directly to the variable internally

Create proc Stuproc
@sname varchar (100) = ' lei '
As
Begin
Select S#,sname,sage,ssex from student where [email protected]
End
Go
--Why Ask Hovertree.com
EXEC Stuproc

You can also export the contents of the variable, using the output

Create proc Stuproc
@sname varchar (100),
@IsRight int output//outgoing parameters
As
If exists (select S#,sname,sage,ssex from student where [email protected])
Set @IsRight =1
Else
Set @IsRight =0
Go
--Why Ask Hovertree.com
DECLARE @IsRight int
exec stuproc ' lei ', @IsRight output
Select @IsRight

The above is the global variable, below to understand the local variable
Local variables are also known as internal variables. A local variable is defined within a function as a description. The scope is limited to the inside of the function, and it is illegal to use the variable after leaving the function.
Definition of a local variable: You must use the DECLARE command before it can be used, declare{@ variable name data type}
Assignment method for local variables: set{@ variable name = expression} or select{@ variable name = expression}
Display of local variables: SELECT @ variable Name

Create proc Stuproc
As
DECLARE @sname varchar (100)
Set @sname = ' Lei '
Select S#,sname,sage,ssex from student where [email protected]
Go
--Why Ask Hovertree.com
EXEC Stuproc

What if we are going to show the data of the local variables?

Create proc Stuproc
As
DECLARE @sname varchar (100)
Set @sname = (select sname from student where s#=01)
Select @sname
Go
--Why Ask Hovertree.com
EXEC Stuproc

Recommendation: http://www.cnblogs.com/roucheng/p/3541165.html

Basic knowledge of stored procedures