SQL Server Series: stored procedures

The main purpose of customer/server development is to process dataProgramMove to the nearest possible place to the data. By moving data processing programs from customer applications to servers, you can reduce network traffic and improve performance and data integrity.

To move the data processing program closer to the data, one of the most common methods is to develop the stored procedure.

1. Manage stored procedures

1.1, create, alter, and drop

Stored procedures are managed by the following data definition language commands: Create, alter, and drop.

Create is used to create a stored procedure. It is the first command in batch processing. The normal completion of batch processing means that the stored procedure is successfully created. The drop command will delete stored procedures from the database, and the alter command will replace the original stored procedures with the newCode. If you want to modify a stored procedure, you 'd better use the alter method instead of deleting it first and then recreating it, because the latter method will lose all the permission settings in the original stored procedure.

 
Create ProcedureCategorylistAsSelectCategorynameFromCategory
 

1.2. Return record set

Stored Procedures can return record sets.

ExecCategorylist
 

1.3 compile the Stored Procedure

Compilation of corresponding stored procedures is automatically performed. During the first execution of the stored procedure, the stored procedure is compiled and the compilation results are saved in the memory. If the server is restarted, all compilation results of all stored procedures will be lost. They will be re-compiled only when they are called again.

SQL Server uses the master. DBO. syscacheobjects table to record compiled objects. To view compiled stored procedures, run the following query:

 Select   Case (C. SQL As   Char ( 35 )) As Storeprocedure, cacheobjtype, usecounts As   Count  From Master. DBO. syscacheobjects C  Join  Master. DBO. sysdatabases d  On C. dbid =  D. dbid  Where D. Name =   Db_name  ()  And Objtype =   '  Proc  '  Order  By Storeprocedure

2. transmit data to the stored procedure

SQL Server Stored Procedures can use many input and output parameters.

2.1 input parameters

If you want to transmit data to a stored procedure using input parameters, you can list the parameters to be added after the stored procedure name in the create procedure command. Each parameter must start with @. Once the input parameter is declared, it becomes a local variable in the stored procedure. Just like declaring local variables, you must use valid data types to define input parameters. When calling a stored procedure, you must provide values for these input variables (unless the input variable has a default value ).