SQL Server database stored procedures Introduction, server Stored Procedures

SQL Server database stored procedures Introduction, server Stored Procedures

What is stored procedure

If you have been familiar with other programming languages, you can understand that the stored procedure is like a method.

Even if he is a method, then he has a similar method name, the variables to be passed by the method and the returned results. Therefore, stored procedures have stored procedure names, stored procedure parameters, and returned values.

Advantages of stored procedures:

The stored procedure capability greatly enhances the functionality and flexibility of the SQL language.

1. Data security and integrity can be ensured.
2. stored procedures allow unauthorized users to indirectly access the database under control to ensure data security.
3. Through the stored procedure, relevant actions can be taken together to maintain the integrity of the database.
4. Before running the stored procedure, the database has analyzed its syntax and provided an optimization execution plan. This compiled process can greatly improve the performance of SQL statements.
6. reduces network traffic.
7. Add the computing program that embodies the enterprise rules to 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

First, let's take a look at the system stored procedures. The system stored procedures are defined by the system and are mainly stored in the MASTER database. The names start with "SP" or start with "XP. Although these system stored procedures are stored in the MASTER database,

However, we can still call system stored procedures in other databases. Some system stored procedures are automatically created in the current database when a new database is created.

Common system stored procedures include:
Copy codeThe Code is as follows:
Exec sp_databases; -- view the database
Exec sp_tables; -- view the table
Exec sp_columns student; -- View Columns
Exec sp_helpIndex student; -- view the index
Exec sp_helpConstraint student; -- Constraint
Exec sp_helptext 'SP _ stored_procedures '; -- view the Statement defined in the creation of a stored procedure
Exec sp_stored_procedures;
Exec sp_rename student, stuInfo; -- change the table name
Exec sp_renamedb myTempDB, myDB; -- change the Database Name
Exec sp_defaultdb 'master', 'mydb'; -- change the default database name
Exec sp_helpdb; -- Database Help, query database information
Exec sp_helpdb master;
Exec sp_attach_db -- Additional database
Exec sp_detach_db -- detach a database

Stored Procedure Syntax:

Before creating a stored procedure, let's take a look at the name of the stored procedure. Several articles about the stored procedure like to add a prefix when creating the stored procedure, it is very important to develop the habit of prefix the stored procedure name. Although this is only a small thing, it is often decided by small details. Some people like to add a prefix like this, for example, proc _ name. The prefix usp _ name is also displayed. The previous proc is short for procedure, and the next sup is user procedure. I like the first one, so all the Stored Procedure Names below are written in the first one. The name is written in the camel naming method.

The syntax for creating a stored procedure is as follows:
Copy codeThe Code is as follows:
Create proc [EDURE] stored procedure name

@ Parameter 1 [data type] = [default value] [OUTPUT]

@ Parameter 2 [data type] = [default value] [OUTPUT]

AS

SQL statement

EXEC process name [parameter]

Use a stored procedure instance:

1. Without Parameters
Copy codeThe Code is as follows:
Create procedure proc_select_officeinfo -- (stored procedure name)
As select Id, Name from Office_Info -- (SQL statement)

Exec proc_select_officeinfo -- (call the Stored Procedure)

2. With input parameters
Copy codeThe Code is as follows:
Create procedure procedure_proc_GetoffinfoById -- (stored procedure name)
@ Id int -- (parameter name parameter type)
As select Name from dbo. Office_Info where Id = @ Id -- (SQL statement)

Exec procedure_proc_GetoffinfoById 2 -- (after the stored procedure name, add a space and parameters. Multiple parameters are separated by commas)

Note: The parameter value assignment is. The first parameter can be left blank, followed by the input parameter. You need to specify the parameter name to be passed in.

3. Parameters with Input and Output
Copy codeThe Code is as follows:
Create procedure proc_office_info -- (stored procedure name)
@ Id int, @ Name varchar (20) output -- (parameter Name parameter type) output parameters must be added with output
As
Begin
Select @ Name = Name from dbo. Office_Info where Id = @ Id -- (SQL statement)
End
Declare @ houseName varchar (20) -- declares a variable and obtains the value passed out by the stored procedure.
Exec proc_office_info -- (stored procedure name)
4, @ houseName output -- (if the @ variable = output is used to add the OUTPUT parameter, an error is returned, so use another method)
Select @ houseName -- (display value)

4. Those with return values
Copy codeThe Code is as follows:
Create procedure proc_office_info -- (stored procedure name)
@ Id int -- (parameter name parameter type)
As
Begin
If (select Name from dbo. Office_Info where Id = @ Id) = null -- (SQL statement)
Begin
Return-1
End
Else
Begin
Return 1
End
End

Declare @ house varchar (20) -- declare a variable and obtain the value passed by the Stored Procedure
Exec @ house = proc_office_info 2 -- (call the stored procedure and use the variable to receive the returned value)
-- Note: stored procedures with return values can only be int-type return values.
Print @ house