SQL Server 7.0 (6)

Create a stored procedure body
The stored procedure logic resides in the stored procedure body. A stored procedure body can contain any Transact SQL statement. The following statements cannot appear in any stored procedure body:
· CREATE DEFAULT
· CREATE TRIGGER
· CREATE PROCEDURE
· CREATE VIEW
· CREATE RULE
1. Local variables
Local variables maintain the median value in the stored procedure. When a value needs to be stored multiple times or the result of a query needs to be used in subsequent queries, local variables must be used. In these cases, values are stored in local variables and can be used for future use. The name of the local variable starts with the "@" symbol. The variable name can contain characters and numbers. Type declaration is required before local variables are used. The SELECT statement is required to assign values to local variables. SELECT can retrieve a value from a table and assign it to a variable, or assign a constant value to the variable. A simple SELECT statement can assign values to multiple local variables.
For example:
DECLARE @ var1 integer, @ var2 varchar (20)
SELECT @ var1 = 32,
@ Var2 = 'myage'
If no data is returned from the SELECT query and the SELECT statement assigns the data value to the local variable, the value of the local variable will not change.
2. Conditional words
The following conditions are provided in the stored procedure:
· IF ...... ELSE statement.
· WHILE statement.
1) IF ...... ELSE statement. The statement contains three parts: Boolean expression, IF statement block, and ELSE statement block. Syntax:
IF (boolen_expr)
{Statements}
ELSE
{Statements}
There can be multiple statements in the IF or ELSE statement block. In this case, BEGIN and END statements are required to mark the statement block.
2) WHILE statement. The WHILE statement is used to process statements that are repeatedly executed until a condition is TRUE. Syntax:
WHILE (boolen_expr)
BEGIN
Statement (s)
BREAK
Statement (s)
CONTINUE
END
The in and END statements indicate the body of the loop. The BREAK statement ends the execution of the loop (that is, after the END statement ). The CONTINUE statement returns the control processing process to the beginning of the loop (that is, the right side of the BEGIN statement ).
Note: If two or more WHILE loops are nested, the internal BREAK exits from the second outer loop. All statements after the internal cycle ends can be executed after the internal cycle ends.
3. GOTO statement
In the execution of stored procedures, statements are executed sequentially. A goto statement is used to break the execution sequence of such statements. It immediately jumps to a statement for execution, and this statement is often not followed by the previous statement. A goto statement is used with a Label to identify a statement.