Mssql stored procedure (1/2)

Advantages of stored procedures:

1. Because the database first compiles and then executes the action. However, the stored procedure is a compiled code block, so the execution efficiency is higher than the T-SQL statement.

2. A stored procedure can replace the large pile of T-SQL statements when the program interacts in the network, so it can also reduce the network traffic and improve the communication rate.

3. Stored procedures allow unauthorized users to indirectly access the database under control to ensure data security.

Summary: Stored procedures are good things. They are essential tools for projects. The following describes the basic syntax of stored procedures.

--------------------------------------------------------------------------------

Explanation of stored procedure syntax and parameters

--------------------------------------------------------------------------------

Some basic syntax of the stored procedure:

 

The code is as follows:	Copy code
-------------- Create a stored procedure ----------------- Create proc [EDURE] procedure_name [; number] [{@ Parameter data_type} [VARYING] [= default] [OUTPUT] ] [,... N] [ {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [For replication] AS SQL _statement [... n]

-------------- Call the stored procedure -----------------

EXECUTE Procedure_name ''-- if a stored procedure has a parameter, the format of the parameter followed by @ parameter name = value can also be directly the parameter value.

-------------- Delete stored procedure -----------------

Drop procedure procedure_name -- you can call another stored procedure in the stored procedure, but cannot delete the parameters for creating a stored procedure in another stored procedure:
1. procedure_name: name of the stored procedure. Add # as a local temporary stored procedure, and add # as a global temporary stored procedure.

2.; 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 example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name.

3. @ parameter: the parameter of the stored procedure. There can be one or more. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined ). A stored procedure can have a maximum of 2.100 parameters.
Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects. For more information, see EXECUTE.

4. data_type: data type of the parameter. All data types (including text, ntext, and image) can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If the specified data type is cursor, the VARYING and OUTPUT keywords must also be specified. For more information about the data types and syntax provided by SQL Server, see data types.
It indicates that there is no limit on the maximum number of output parameters that can be cursor data types.

5. VARYING: specify the result set supported by the output parameter (the result set is dynamically constructed by the stored procedure and the content can be changed ). Only applicable to cursor parameters.

6. default: The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or NULL. If the LIKE keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]).

7. OUTPUT: indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. The OUTPUT parameter can be used to return information to the call process. The Text, ntext, and image parameters can be used as OUTPUT parameters. The OUTPUT parameter using the OUTPUT keyword can be a placeholder cursor.

8. RECOMPILE: indicates that SQL Server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the RECOMPILE option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.

9. ENCRYPTION: indicates the entries in the SQL Server encrypted syscomments table that contain the CREATE PROCEDURE statement text. ENCRYPTION prevents the process from being published as part of SQL Server replication. It indicates that during the upgrade, SQL Server uses the encryption annotation stored in syscomments to recreate the encryption process.

10. for replication: specifies that the stored procedure created for replication cannot be executed on the subscription server .. Stored procedures created using the for replication option can be used FOR filtering stored procedures and can only be executed during REPLICATION. This option cannot be used WITH the with recompile option.

11. AS: specifies the operation to be performed in the process.

12. SQL _statement: any number and types of Transact-SQL statements to be included in the process. But there are some restrictions.

Homepage 1 2 Last page