SQL Server -- Stored Procedure

Storage, we immediately remember the warehouse of various items that we encountered around us, that is, how the Stored Procedure stores items in the warehouse. This is a stored procedure in our life. However, in a database, a stored procedure is an object and an efficient and secure way to access the database. Well, next we will summarize the knowledge points of the stored procedure in the database. We can summarize these three aspects: Overview, Implementation, and Management.

 

 

I. Overview of stored procedures:

1, concept: Stored Procedure (storedprocedure) is a set of T-SQL statements executed on the database server side, compiled and stored in the database server. It can return data to users, write or modify data to the database, and perform system functions and management operations.

 

2. Advantage: A. Fast execution speed:

B. modular program design

C. reduce network communication volume

D. Ensure system security

E. Implement code reuse.

 

3. Classification: A. User Stored Procedure

B. System stored procedures

C. extended stored procedure: it is implemented by a dynamic link library (DLL, dynamic-link librar-ies) executed outside the SQL server environment. It is identified by the prefix "XP.

Here, you can use Micosoft SQL Server Management studio to add, delete, modify, and query operations. The operation steps are almost the same and I will not write them here. Which example can be used to practice well. The following is a summary of some functions for the T-SQL.

 

2. Implement the stored procedure:

1. Create (using createprocedure ):

A. Simple stored procedure:

Createproc [edure] procedure_name [; number]

[{@ Parameter data_type}

[Varying] [= default] [Output] [, N…]

[

{Recompile | encryption | encompile, encryption}]

[For replication]

As SQL _statement [...... N]

Parameter description: 1. Name of the new stored procedure in procedure_name; 2. number, which is an optional integer used to import the process with the same name

Row grouping; 3, @ parameter; 4, Data Type of the data_type parameter; 5, varying indicates

Set as the result set supported by the output parameter, which is only applicable to the cursor parameter; 6. Default Value of the default parameter;

7. Default Value of the output parameter; 8. recompile indicates that sqlserver will not cache the plan.

Re-compile during runtime. 9. As: Specifies the operations to be performed during the process; 10. SQL _statament, and t-SQL statements.

B. Stored Procedures with parameters: during creation, we need to define parameters in the process.

C. 1 to be returned, you can use the print Print statement to raise 2, or use the raiserror function to return the error message.

3. You can use the return statement to exit the process or query unconditionally!

 

2. Execute the stored procedure:

A: Execute through execute or exec statements. Syntax:

[Exec [ute] {[@ return_status] = procedure_name [; number] | @ procedure_name_var}

[[@ Parameter =] {value | @ variable [Output] | [Default]}] [, N…]

[With recompile]

 

Parameter description: 1, @ return_status, the returned status of the stored procedure, 2, with recompile force compile the new plan.

The other values are the same as those in the creation process.

 

 

3. Manage stored procedures:

1. View: Use sp_helptext to view the text information of the stored procedure, use sp_depends to view the relevance of the stored procedure, and use

Sp_help to view the general information about the stored procedure.

Syntax: sp_helptext [@ objname =] 'name' (Object Name)

Sp_depends [@ objname =] 'object' (database object to be checked for relevance)

Sp_help [[@ objname =] Name]

 

2. Modify: Change "CREATE" in the creation to "alter", which is the modified syntax. This does not change the permission or the name.

Rename: sp_rename can be used to change the name of an object (table, stored procedure, trigger, column, etc.) created by a data user;

Syntax: sp_rename [@ objname =] 'objext _ name'

[@ Newname =] 'new _ name'

[, @ Objtype =] 'object _ type'

 

3. Delete: You can use the drop procedure statement to conveniently Delete the data.

 

4. recompile:

A. Set re-compilation when creating the stored procedure, and specify with recompile when defining

B. Set re-compilation when executing the stored procedure, and specify with recompile during execution.

C. Use the system stored procedure settings to re-compile. sp_recompile [@ objname =] 'object ']

 

Here, the stored procedure reminds me of the procedures, functions, and modules in VB. I think they are very similar, and their functions are worth mentioning. Good use of stored procedures will make our programs very easy to understand.