SQL Server Stored Procedures and triggers

I used to understand concepts. I have no in-depth understanding. I found some materials for study last night. Take notes on your understanding. When you are doing something, you can directly splice SQL statements with databases, and rarely use stored procedures and parameters.

Stored Procedure: a set of T-SQL statements stored in the database, which can be called multiple times in the program by compiling once. In addition, parameters and output parameters can be transmitted to the statement to prevent injection attacks. The stored procedure is more efficient and reduces network traffic.

Definition Syntax of stored procedure:

Create procedure procedure_name [; number]

(

@ Parame1 data_type [output],

......

)

AS

SQL statement

 

 

Procedure_name: name of the stored procedure

[; Number]: an optional integer used to group processes with the same name, so that the same group can be removed with a drop procedure statement. For exampleProc; 1,Proc; 2. When you want to delete this set of stored procedures, you can use DROP PROCEDUREOrderprocThe statement removes the entire group.

@ Parame: defines the parameter name.

Data_type: Define the parameter type

Output: an optional parameter. When the output limit is added after the parameter, this parameter is specified as the returned parameter.

 

 

Trigger: A trigger is created only for insert, update, and delete operations on a table. The trigger is automatically executed.

Trigger Syntax:

Create trigger name

On indicates

For [insert | update | delete]

As

SQL statement

 

The difference between a trigger and a stored procedure: a trigger does not need to be called in a program. It is automatically triggered when the corresponding operation is performed on the table. The stored procedure is compiled and called directly in the program if necessary.