Getting Started with SQL Server 7.0 (vi)---establishing a stored procedure body

server| stored procedures to create a stored procedure body
Stored procedure logic resides in the stored procedure body. A stored procedure body can contain any bar of Transact SQL statements. The following Transact SQL statement cannot appear in any stored procedure body:
· CREATE DEFAULT
· CREATE TRIGGER
· CREATE PROCEDURE
· CREATE VIEW
· CREATE rule
1. Local Variables
The local variable holds the middle value of the stored procedure. Local variables are required when a value is required more than once in a stored procedure, or when the results of a query need to be used in subsequent queries. In these cases, the values are stored in local variables and can be used for future use. The name of the local variable begins with the "@" symbol. The name of a variable can contain characters and numeric values. A local variable requires a type declaration before it is used. Assigning a local variable requires the use of a SELECT statement. Select can retrieve a value from a table and assign it to a variable, or you 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 a select query, and the select also assigns the value of the data to a local variable, the value of that local variable will not change.
2, conditional words
The conditional statements provided in the stored procedure include:
· IF ... Else statement.
· The while statement.
1 IF ... Else statement. There are three parts in the statement: A Boolean operation expression, an if statement block, and an else statement block. The syntax is as follows:
IF (boolen_expr)
{statements}
ELSE
{statements}
There can be more than one statement in an if or else statement block, in which case the statement begin and end are required to flag the statement block.
2) while statement. The while statement is used to handle statements that are repeated before a condition is true. The syntax is as follows:
while (boolen_expr)
BEGIN
Statement (s)
Break
Statement (s)
CONTINUE
End
The BEGIN and END statements flag the loop body. The break statement ends the execution of the loop (that is, after the end statement). The Continue statement returns the control process back to the beginning of the loop (the right of the BEGIN statement).
Note: If there are two or more while loops nested, the internal break exits the secondary outer loop. All statements after the end of the internal loop cannot continue until the internal loop is executed.

3. Goto statement
In the execution of the stored procedure, the statements are executed sequentially. A goto statement is used to break the order in which the statement is executed, and it immediately jumps to execution on a statement that is often not immediately following the previous statement. A goto statement is used with a flag (Label) that 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
The return statement is used for unconditional exit of the stored procedure. Any statements after return are no longer executed. The return statement returns a value to the calling statement, but cannot return a null value. SQL Server often returns a status value for a stored procedure. If executed successfully, returns a 0 and, if an error occurs, returns an error code that is negative.
Error code returned by stored procedure
Value
Description
Value
Description

0
Process Execution Successful
-8
There's been a non-fatal internal problem.

-1
Missing the object
-9
Reached the limit of the system

-2
A data type error occurred
-10
A fatal internal inconsistency error has occurred

-3
The process was chosen as the victim of the deadlock.
-11
A fatal internal inconsistency error has occurred

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

-5
A syntax error has occurred
-13
Database is corrupted

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

-7
Resource errors, such as insufficient space


5. Using cursors (CURSOR) is useful when you need a line of processing. Cursors can open a result set (rows selected according to the specified criteria) and provide functionality that is handled one line at a result set. Based on the type of the cursor, it can be rolled back or forward. You need 5 steps to use a cursor: