SQL Stored procedure instances and related basic knowledge

Let's take a look at the knowledge about stored procedures.

1. Create a stored procedure
Create procedure stored procedure name
Parameter list
As
Begin
......
End
II. Call the stored procedure
Call stored procedure name ()
III. Delete stored procedures
Drop procedure stored procedure name
Note: You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.
4. Other common commands
1. show procedure status
Displays basic information about all stored procedures in the database.
2. show create procedure stored procedure name
Displays detailed information about a stored procedure.

5. Notes
Single line comment :--
Multi-line comment:/* comment content */
VI. Block definition
Begin
Code block
End
The begin and end here are equivalent to {and} in C language}
7. Execute other stored procedures EXEC
Example: EXEC dbo. [SalesByYear] @ BeginDate = '2014/1/90', @ EndDate = '2014/3/08 ';

------------------------------ Data type ------------------------------
I. Basic data types
You can view related data types in SQL Management.
II. Variables
1. Custom variable: declare @ variable name data type;
2. Variable assignment: set @ variable name = value;
III. Operators
1. Arithmetic operators
+,-, *,/, DIV (for example, 10 DIV 3 value is 3), % (modulo)
2. Comparison operators
>,<, <=, >=, Between and, not between and, in, not in, =, <> |! =, <=> (Strictly compare whether two NULL values are equal, for example: NULL <=> NULL true), LIKE, REGEXP, is null, IS NOT NULL
3. Bitwise operators
|, &, <,> ,~
------------------------------ Process control ------------------------------
I. Ordered structure
II. Branch structure
If else
Case
III. Loop structure
For loop
While loop
Loop
Repeat until Loop
------------------------------ Input and output ------------------------------
Three parameter types: INPUT, OUTPUT, and INPUTOUTPUT
INPUT parameters
Indicates that the parameter value must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.
OUTPUT parameters.
Indicates that the value of this parameter can be returned after being changed within the stored procedure.
INPUTOUTPUT input and output parameters
This parameter is specified during the call and can be changed or returned.

Learning started
Requirement 1: query the stored procedure of bankMoney

The code is as follows:	Copy code
Create procedure sp_query_bankMoney As Select * from bankMoney Go Exec sp_query_bankMoney

Note * you only need to replace the SQL statement with the name of the stored procedure during usage, which is convenient!

Instance 2 (passing parameters to the stored procedure ):

Add a record to the bankMoney table and query the total amount of all deposits in the table userID = Zhangsan.

The code is as follows:

Copy code

Create proc insert_bank @ param1 char (10), @ param2 varchar (20), @ param3 varchar (20), @ param4 int, @ param5 int output With encryption --------- encryption As Insert bankMoney (id, userID, sex, Money) Values (@ param1, @ param2, @ param3, @ param4) Select @ param5 = sum (Money) from bankMoney where userID = 'hangsan' Go You can run this stored procedure in the SQL Server query analyzer: Declare @ total_price int Exec insert_bank '004 ', 'hangsan', 'mal', 100, @ total_price output Print 'total balance is '+ convert (varchar, @ total_price) Go

Here again ?? What's wrong? Sputum? Return value (so that users who are reading this example do not have to view the syntax ):

1. Return an integer with Return
2. Return parameters in output format
3. Recordset

Differences between return values:

Both output and return can be received using variables in a batch program, while recordset is passed back to the client that executes the batch.

Example 3: simple process with complex SELECT statements

The following stored procedure returns all authors (names provided), published books, and publishers from the join of the four tables. This stored procedure does not use any parameters.

The code is as follows:	Copy code
USE pubs If exists (SELECT name FROM sysobjects WHERE name = 'au _ info_all 'AND type = 'p ') Drop procedure au_info_all GO Create procedure au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a inner join titleauthor ta ON a. au_id = ta. au_id inner join titles t ON t. title_id = ta. title_id inner join publishers p ON t. pub_id = p. pub_id GO

The au_info_all stored procedure can be executed in the following ways:

The code is as follows:	Copy code
EXECUTE au_info_all -- Or EXEC au_info_all

If the process is the first statement in batch processing, you can use:

The code is as follows:	Copy code
Au_info_all

Example 4: simple process with parameters

The code is as follows:	Copy code
Create procedure au_info @ Lastname varchar (40 ), @ Firstname varchar (20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a inner join titleauthor ta ON a. au_id = ta. au_id inner join titles t ON t. title_id = ta. title_id inner join publishers p ON t. pub_id = p. pub_id WHERE au_fname = @ firstname AND au_lname = @ lastname GO

The au_info stored procedure can be executed in the following ways:

The code is as follows:	Copy code
EXECUTE au_info 'dull ', 'Ann' -- Or EXECUTE au_info @ lastname = 'dull ', @ firstname = 'Ann' -- Or EXECUTE au_info @ firstname = 'Ann ', @ lastname = 'dull' -- Or EXEC au_info 'dull ', 'Ann' -- Or EXEC au_info @ lastname = 'dull ', @ firstname = 'Ann' -- Or EXEC au_info @ firstname = 'Ann ', @ lastname = 'dull'

If the process is the first statement in batch processing, you can use:

The code is as follows:	Copy code
Au_info 'dull', 'Ann' -- Or Au_info @ lastname = 'dull ', @ firstname = 'Ann' -- Or Au_info @ firstname = 'Ann ', @ lastname = 'dull'

Example 5: simple process with wildcard parameters

The code is as follows:	Copy code
Create procedure au_info2 @ Lastname varchar (30) = 'd % ', @ Firstname varchar (18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a inner join titleauthor ta ON a. au_id = ta. au_id inner join titles t ON t. title_id = ta. title_id inner join publishers p ON t. pub_id = p. pub_id WHERE au_fname LIKE @ firstname AND au_lname LIKE @ lastname GO

The au_info2 stored procedure can be executed in multiple combinations. Only some combinations are listed below:

The code is as follows:	Copy code
EXECUTE au_info2 -- Or EXECUTE au_info2 'wh %' -- Or EXECUTE au_info2 @ firstname = 'a %' -- Or EXECUTE au_info2 '[CK] ars [OE] N' -- Or EXECUTE au_info2 'hunter', 'Sheryl' -- Or EXECUTE au_info2 'H % ','s %' = 'Proc2'