Stored Procedure Learning

What is a stored procedure?

Definition:
A Stored Procedure refers to a groupCodeUnits are stored together with T-SQL statements in the database. You can use input parameters to pass in runtime information and retrieve data that is used as a result set or output parameter.

advantages of stored procedures:
1. Using Stored Procedures can speed up database execution. The stored procedure will be compiled at the first run. This generates an execution plan-a record of the steps that Microsoft SQL server must take to get results specified by the T-SQL during a stored procedure. Then, the execution plan is cached in the memory for future use. This will improve the performance of stored procedures, because SQL server does not need to re-analyze the code to determine how to process it, but simply reference the cache plan. The cache plan remains available until SQL server restarts or until it overflows memory due to low usage.
2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.
3. stored procedures can be reused to reduce the workload of database developers
4. high security. You can set that only one user has the right to use the specified stored procedure. They can prevent some types of SQL insert attacks

Types of stored procedures:
1. system stored procedure: starts with SP _. It is used to set the system and obtain information. related management work. For example, sp_help is used to obtain information about the specified object.
2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.
Exec master .. xp_mongoshell 'Ping 10.8.16.1'
3. User-Defined stored procedures, which we refer to as stored procedures
Common formats

Create   Procedure Procedue_name
[ @ Parameter data_type ] [ Output ]
[ With ] {Recompile | Encryption}
As
SQL _statement

Explanation:
Output: By using the output parameter to return scalar data, You Can slightly increase the speed and save a small amount of processing power. In the ApplicationProgramIf you want to return a single value, try this method.
With {recompile | encryption}
Recompile: Indicates re-compiling every time this stored procedure is executed.
Encryption: the content of the created stored procedure is encrypted.
Example:
In the following example, a parameter @ username is input to determine whether the user exists.

-- -----------------------------------------------------------------------------
-- Determine whether a user exists
-- -----------------------------------------------------------------------------
Create   Proc Isexistuser
(
@ Username   Varchar ( 20 ),
@ Isexisttheuser   Varchar ( 25 ) Output -- output parameters
)
As
Select   @ Isexisttheuser   =   Count (Username)
From Users
Where Username =   @ Username
Go

The following is an example of a stored procedure combining transactions. Adding a user involves two tables, one user data table and one password table: -- -----------------------------------------------------------------------------
-- Add users
-- -----------------------------------------------------------------------------
Create   Procedure Adduser
(
@ Username   Varchar ( 20 ), -- User Name
@ Passwords   Varchar ( 18 ), -- Password
@ Email   Varchar ( 100 ), -- Mailbox
@ Qq   Varchar ( 11 ), -- QQ number
@ Imageid   Int -- Avatar
)
As
-- Set nocount to on, so that no statistical information is returned. (Some statements contained in the stored procedure do not return much actual data. Therefore, this setting greatly reduces network traffic, therefore, the performance can be significantly improved .)
Set Nocount On

Declare   @ Currenterror   Int

-- Create transactions and insert data to two tables
Begin   Transaction

-- Create user information
Insert   Into Users (username, email, QQ, imageid)
Values ( @ Username , @ Email , @ Qq , @ Imageid )

Select   @ Currenterror   =   @ Error

If   @ Currenterror   ! =   0
Begin
Goto Error_handler
End

-- Create User Password
Insert   Into Signon (username, [ Password ] )
Values ( @ Username , @ Passwords )

Select   @ Currenterror   =   @ Error

If   @ Currenterror   ! =   0
Begin
Goto Error_handler
End

-- End transaction
Commit   Transaction

-- Set nocount to off
Set Nocount Off

-- If the return value is 0, the operation is successful. Otherwise, the operation fails.
Return   0

-- Error Handling
Error_handler:
Rollback   Transaction
Set Nocount Off
Return   @ Currenterror
Go Note When writing the stored procedure. You may not always maintain this code. However, other people may want to know its purpose in the future. 'Nuff once said this.