Basic knowledge record of stored procedures in SQL Server

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