Getting Started with 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 Transact SQL statements cannot appear in any stored procedure body:
· CREATE DEFAULT
· CREATE TRIGGER
· CREATE PROCEDURE
· CREATE VIEW
· CREATE RULE
1, local variables
local Variables hold stored procedure intermediate values. Local variables are used when a value is needed multiple times in a stored procedure, or when the results of a certain query need to be used in subsequent queries. In these cases, the value is stored in a local variable and is available for future use. The names of local variables begin with the "@" symbol. Variable names can contain characters and numeric values. Local variables require type declaration before use. Assigning values ​​to local variables requires the use of the SELECT statement. SELECT can retrieve a value from a table and assign it to a variable, or it can assign a constant value to a 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 SELECT assigns the value of the data to a local variable, so the value of the local variable will not change.
2. Conditional words
The conditional statements provided in the stored procedure include:
·IF...ELSE statement.
· WHILE statement.
1) IF...ELSE statement. This statement contains three parts: Boolean operation expression, IF statement block and ELSE statement block. The syntax is as follows:
IF (boolen_expr)
{statements}
ELSE
{statements}
There can be multiple statements in the IF or ELSE statement block. In this case, the statements BEGIN and END to mark the statement block.
2) WHILE statement. The WHILE statement is used to process statements that are executed repeatedly until a certain condition is TRUE. The syntax is as follows:
WHILE (boolen_expr)
BEGIN
statement(s)
BREAK
Statement(s)
CONTINUE
END
BEGIN and END statements mark the loop body. The BREAK statement ends the execution of the loop (i.e. goes after the END statement). The CONTINUE statement returns control processing to the beginning of the loop (to the right of the BEGIN statement).
                                                                                                                                                                                                                                                                                                      to be All statements after the end of the inner loop can continue to execute after the inner loop is executed.

3. GOTO statement
In the execution of the stored procedure, the statements are executed sequentially. The GOTO statement is used to break the order of execution of such statements. It immediately jumps to a certain statement for execution, and this statement often does not immediately follow the previous statement. The GOTO statement is used with a label (Label), which identifies a statement.
For example:
USE pubs
GO

DECLARE @num int
SELECT
IF @num = 0
GOTO Err
ELSE
BEGIN
PRINT 'authors found'
SELECT * FROM authors
GOTO Last
END
Err: PRINT 'no authors found'
Last: PRINT 'Finish execution'
GO
4, RETURN statement
RETURN statement Exit the stored procedure unconditionally. Any statements after RETURN will no longer be executed. The RETURN statement can return a value to the calling statement, but it cannot return a NULL value. SQL Server often returns a status value for stored procedures. If the execution is successful, a 0 is returned. If an error occurs, a negative error code is returned.
The error code returned by the stored procedure
Value
Said It means
Value
Said It means

0
The process was executed successfully
-8
It happened Non-fatal internal problem

-1
Object missed
-9
System limit reached

-2
Data type error occurred
-10
A fatal internal inconsistency error occurred

-3
The process was chosen to be the victim of a deadlock
-11
A fatal internal inconsistency error occurred

-4
A permission error occurred
-12
The table or index is corrupted

-5
A syntax error occurred
-13
Database is destroyed

-6
A mixed user error occurred
-14
A hardware error occurred

-7
Resource error, such as insufficient space, etc.


5. Use a cursor (CURSOR). Cursors are very useful when you need to process rows one by one. Cursors can open a result set (rows selected according to specified criteria) and provide the ability to process the result set row by row. Based on the type of cursor, it can be rolled back or forwarded. There are 5 steps required when using cursors:

The above is the content of SQL Server 7.0 Getting Started (6). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!