Introduction to SQL stored procedure

Time:2021-1-25

Overview of stored procedures

Stored procedure is a group of SQL statements to complete specific functions. It is a kind of programmable object of database, similar to a function

It is fast, flexible and safe

System stored procedure

The built-in stored procedure of SQL Sever is stored in the master library. It is mainly used to perform some functions of SQL Sever and display information about database and user. The system stored procedure name issp_ At the beginning, you can execute system stored procedures in any database

User stored procedure: a stored procedure created by the user and stored in the user database

 

Create stored procedure

 

 

Creating a parameterless stored procedure

Create proc P1 as SQL statement to be executed

Exec P1 -- execute

Creating stored procedures with input parameters

Create proc stored procedure name
@Input parameter name data type -- input parameter definition
As the SQL statement to execute

--Call
Exec stored procedure name parameter value -- 1 exec can be omitted
Exec stored procedure name @ input parameter name = parameter value -- 2 exec can be omitted

Declare @ input parameter name parameter type -- 3 exec cannot be omitted
Exec stored procedure name parameter value

 

Creating stored procedures with output parameters

Create proc stored procedure name
@Input parameter name data type -- input parameter definition
@Output parameter name data type output -- output parameter definition
As the SQL statement to be executed (including the statement: print @ output parameter name)

--Call
Declare @ output parameter name parameter type 
Exec stored procedure name, input parameter value, @ output parameter name

 

Modifying stored procedures

Alter proc [edure] stored procedure name [; number]
 [{@ parameter name data type} [varying] [= Default]
[ output ] ] [ ,...n ]
as
SQL statement [... N] - the parameters are the same as the create procedure statement

 

Delete stored procedure

Drop procedure name