Stored procedures for SQL Server

Following the completion of the previous database paging function, a stored procedure query and application for SQL Server database was started this week. Previously, only the MySQL database and Oracle database were known, and the knowledge of stored procedures was only used in the Oracle database. I didn't know much about SQL Server database, so I started out a little bit and read some information. Understanding stored procedures is actually a combination of some query statements with the transaction of the database, so that we can invoke the result set in the stored procedure directly when we use it, instead of writing an SQL statement every time we perform an operation. Some of the following information is from the blog Garden Garden Friends of the best garden

Http://www.cnblogs.com/jiajiayuan

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.

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

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

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

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

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

EXEC Stuproc

Stored procedures for SQL Server