Talking about the stored procedure of database

Talking about the stored procedure of database What is a stored procedure

If you've been exposed to other programming languages, it's good to understand that stored procedures are like methods.

He's the way he is. Then he has a similar method name, a method to pass the variable and return the result, so the stored procedure has a stored procedure name with a stored procedure parameter also has a return value.

　Advantages of stored procedures:

• The ability of stored procedures greatly enhances the functionality and flexibility of the SQL language.
• Ensures the security and integrity of your data.
• Stored procedures enable users who do not have permissions to access the database indirectly under control, thus guaranteeing the security of the data.
• Stored procedures allow related actions to occur together to maintain the integrity of the database.
• Before running the stored procedure, the database has been analyzed by syntax and syntax, and the optimized execution scheme is given. This compiled process can greatly improve the performance of SQL statements.
• You can reduce the amount of traffic on your network.
• The operational procedures that embody enterprise rules are placed in the database server for centralized control.

Stored procedures can be divided into system stored procedures, extended stored procedures, and user-defined stored procedures

System Stored Procedures

Let's take a look at the system stored procedures, system stored procedures are defined by the system, mainly in the master database, the name starts with "SP" or "XP". Although these system stored procedures are in the master database,

However, we can still invoke system stored procedures in other databases. There are some system stored procedures that are automatically created in the current database when a new database is created.

Common system stored procedures are:

• exec sp_databases; --View Database
• exec sp_tables; --View Table
• EXEC sp_columns student;--view columns
• EXEC sp_helpindex student;--View index
• EXEC sp_helpconstraint student;--constraints
• exec sp_helptext ' sp_stored_procedures ';--View the statement of the stored procedure creation definition
• exec sp_stored_procedures;
• EXEC sp_rename student, stuinfo;--change table name
• exec sp_renamedb mytempdb, mydb;--change database name
• exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for logins
• EXEC sp_helpdb;--database help, querying database information
• exec sp_helpdb master;
• EXEC sp_attach_db--Attaching a database
• EXEC sp_detach_db--Detaching the database

Stored procedure syntax:

Before creating a stored procedure, the first to say the name of the stored procedure, see a few of the stored procedure articles are like to add a prefix in the creation of stored procedures, the habit of adding a prefix to the stored procedure name is very important, although this is only a small thing, but often small details determine big success or failure. See some people like this prefix, such as proc_ name. Also see this add-on prefix usp_ name. The former proc is a shorthand for procedure, and the latter sup means user procedure. I prefer the first one, so all the following stored procedure names are written in the first form. The name is written using the Camel name method.

The syntax for creating a stored procedure is as follows:

CREATE proc[edure] Stored procedure name @ parameter 1 [data type]=[Default] [OUTPUT] @ parameter 2 [data type]=[Default] [Output]as SQL statement EXEC procedure name [parameter] 

Using a stored procedure instance: 1. Without parameters

CREATE PROCEDURE proc_select_officeinfo--(stored procedure name)asselect from office_info-- (SQL statement) EXEC proc_select_officeinfo--(call stored procedure)

2. With input parameters

CREATE PROCEDURE Procedure_proc_getoffinfobyid--int--(parameter type)asSelect  fromwhere [email protected]2-(after the stored procedure name, spaces plus parameters, multiple parameters separated by commas)

Note: The parameter assignment is that the first argument can not write the parameter name, followed by the parameter, need to explicitly pass in which parameter name

3. With input and output parameters

CREATE PROCEDURE proc_office_info--(stored procedure name)
@Id int, @Name varchar (output--) (parameter type) outgoing parameter to add output
As
Begin
Select @Name =name from dbo. Office_info where [email protected]--(SQL statement)
End

DECLARE @houseName varchar (20)--Declare a variable to get the value of the stored procedure
EXEC proc_office_info--(stored procedure name)
4, @houseName output--(legend parameter to add output this side if the @ variable = output will be an error, so in another way)

Select @houseName-(display value)

4. With the return value

CREATE PROCEDURE proc_office_info--(stored procedure name) @Idint--(parameter type of argument name) asbeginif(SelectName fromDbo. Office_infowhere[Email protected]) =NULL--(SQL statement) BEGINreturn-1EndElsebeginreturn 1endenddeclare @house varchar ( -) --declares a variable that gets the value of the stored procedure exec @house=proc_office_info2--(call stored procedure, receive return value with variable)--Note: A stored procedure with a return value can only be a return value of type int print @house

Talking about the stored procedure of the database