Database Stored Procedure (procedure), stored Procedure procedure

Database Stored Procedure (procedure), stored Procedure procedure
Advantages of a stored procedure:

• System stored procedures

• Custom Stored Procedure

• Extended Stored Procedure

Format of the stored procedure:

-- Template generated from Template Explorer using:-- Create Procedure (New Menu).SQL---- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below.---- This block of comments will not be included in-- the definition of the procedure.-- ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:      <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>     -- Add the parameters for the stored procedure here    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,     <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    -- Insert statements for procedure here    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>ENDGO

The stored procedure is a compiled SQL statement. If an SQL statement is written in the code, it must be compiled in time before it can be run. It is compiled at runtime, which increases the time, it also increases the network traffic between the client and the server.

Number of communications between the client and the server without using the stored procedure:

Number of times the client and server communicate with each other using the stored procedure:

After a stored procedure is used, the number of communications between the client and the server decreases. The server only returns a parameter indicating whether the execution is successful or not.

Under what circumstances does Stored Procedure work?

• Complex Database Operations;

• Optimization is required to speed up software running;

• When software security requirements are high;

The compiled stored procedure can be reused by many people!