SQL: Stored Procedure

1/What are stored procedures and concepts

A stored procedure in Transact-SQL, very similar to. NET language, which can be called repeatedly. After the stored procedure executes once, the statement can be cached so that the statements in the cache are used directly at the next execution. This can improve the performance of your stored procedures.

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; NET in its own encapsulated method, when needed to call;

　　Ø The concept of stored procedures

stored procedure procedure is a set of SQL statements that are compiled to complete a particular function, stored in a database, and executed by specifying the name of the stored procedure and giving parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, the stored procedure runs faster than a single SQL statement block. At the same time, because it only needs to provide the stored procedure name and the necessary parameter information in the call, it can reduce the network traffic and the simple network burden to some extent.

　　

Advantages of 2\ stored procedures

A, stored procedure allows standard component-type programming

After a stored procedure is created, it can be invoked more than once in a program, without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, but have no effect on the application source code, which greatly improves the portability of the program.

B, the stored procedure can achieve faster execution speed

If an operation contains a large number of T-SQL statement code that is executed more than once, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives the storage plan in the system tables that are eventually present when a stored procedure is first run. A batch of T-SQL statements needs to be precompiled and optimized each time it is run, so it will be slower.

C, stored procedures to reduce network traffic

For the same operation on a database object, if the T-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client, the call statement is passed in the network, otherwise it will be multiple SQL statements. This reduces network traffic and lowers Network load.

D, stored procedures can be used as a security mechanism to make full use of

The system administrator can restrict the execution of a stored procedure, which can restrict some data access, avoid unauthorized users ' access to the data, and ensure the security of the data.

Summary: 1: Stored procedures, like methods, can be reused, which is also invisible to improve the efficiency of development; (High development efficiency)

2: Because the stored procedure is precompiled, when the stored procedure is first run, the query optimizer analyzes and optimizes it, and the final storage plan exists in the system table, and the SQL statement is precompiled and optimized each time it is run, so it is slower than the stored procedure; (Running fast)

3: Because SQL statements are transmitted over the network, if you write a query for ten tables or more complex operations, the resulting string is huge. If you use stored procedures, you only need to call the name of the stored procedure, which is also to reduce network traffic, reduce Network load

4:d

Ø System Stored Procedure

System stored procedures are system-created stored procedures designed to facilitate querying information from system tables or to complete administrative tasks or other system administration tasks related to updating database tables. System stored procedures are primarily stored in the master database and stored procedures that begin with an "SP" underscore. Although these system stored procedures are in the master database, 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:

execsp_databases;--View DatabaseexecSp_tables;--View TableexecSp_columns student;--View ColumnsexecSp_helpindex student;--View IndexexecSp_helpconstraint student;--ConstraintsexecSp_stored_procedures;execSp_helptext'Sp_stored_procedures';--View stored procedure creation, definition statementsexecsp_rename student, Stuinfo;--Modify the name of a table, index, columnexecSp_renamedb mytempdb, MyDB;--Change the database nameexecSp_defaultdb'Master','MyDB';--Change the default database for loginsexecsp_helpdb;--database Help, querying database informationexecsp_helpdb master;

common system stored procedures

--Table Renamingexecsp_rename'Stu','Stud';Select *  fromStud;--Rename namingexecsp_rename'Stud.name','SName','column';execSp_help'Stud';--Renaming an indexexecSp_rename N'Student.idx_cid'N'Idx_cidd'N'Index';execSp_help'Student';--querying all stored proceduresSelect *  fromSys.objectswhereType= 'P';Select *  fromSys.objectswhereType_desc like '%pro%'  andName like 'sp%';

system Stored Procedure example:

Ø user-defined stored procedures

1. Create a grammar

 create  proc  |  procedure   Pro_name  [ {@ parameter data type} [= default value   output     } [ = default  ]  [ output    as   sql_statements  

　2. Create a stored procedure with no parameters

Create Proc Proc_userinfoselect  as begin Select *  from UserInfo End
--Invoke, execute stored procedure
EXEC Selectuserinfo

　3. Modifying stored Procedures

 alter  proc   stored procedure name  as  begin   statement to be executed .....  end  --query id=1 data  alter  proc   Proc_userinfoselect  as     begin  select  *  from  UserInfo where  id1  ;  end  

　4. Delete stored Procedures

--keyword keyword    stored procedure name drop  procedure  Proc_userinfoselect　　

　　5. Create a stored procedure with parameters

--Paging query DataCreate procProc_userinfobypagingselect (@pageIndex int,--Current page    @pageSize int,--Show N data per page    @count intOutput--How many data, the value returned) asbegin  Set @count=(Select COUNT(*) fromUserInfo); Select *  from(Select *, Row_number () Over(Order  byID) asNum fromUserInfo asTWhereT.numbetween(@pageIndex-1)*@pageSize+1  and @pageIndex*@pageSize;End

6. Do not cache stored procedures

Create proc proc_usersss  with Recompile  as begin Select *  from UserInfo; End

7. Encrypt stored procedures

Create proc Proc_userinfoselect  with Encryption  as begin Select *  from UserInfo; End exec ' proc_usersss ' exec ' Proc_userinfoselect '

　　

SQL: Stored Procedure