SQL Server Stored Procedure details

◆ Advantages:
The execution speed is faster. The stored procedure is compiled only when it is created. Generally, the SQL statement is compiled every time it is executed. Therefore, the execution speed of stored procedures is faster.
When stored procedures are used to process complex operations, the program is more readable and the network burden is less.
Better transaction performance is encapsulated using stored procedures.
It can be effectively injected to improve security.
High maintainability. When some business rules change, you only need to adjust the stored procedure without modifying or re-editing the program.
Better code reuse.

◆ Disadvantages:
The stored procedure puts additional pressure on the server.
It is difficult to maintain a large number of stored procedures.
Poor portability, which is difficult to upgrade to different databases.
Debugging is troublesome and the SQL language processing function is simple.

We recommend that you use stored procedures for complex operations or SQL statements that require transaction operations. stored procedures are not recommended for SQL statements with many parameters and simple operations.

Stored Procedure Definition

A stored procedure is a set of Transact-SQL statements that can be compiled once and executed multiple times later. The execution of stored procedures can improve performance because you do not need to re-compile the statements.
A trigger is a special stored procedure that you cannot directly call. When creating a trigger, it is defined as triggered when a specific type of data is modified for a specific table or column.

Stored Procedure Design Rules

The create procedure statement can include any number of SQL statements and types, except the following statements.

These statements cannot be used anywhere in the stored procedure.
Create aggregate, create rule, create default, create schema, CREATE or alter function, CREATE or alter trigger, CREATE or alter procedure, CREATE or alter view, set parseonly, SET SHOWPLAN_ALL, SET SHOWPLAN_TEXT, SET SHOWPLAN_XML, USE database_name

Other database objects can be created in the stored procedure. You can reference an object created in the same stored procedure as long as it has been created at the time of reference.
You can reference a temporary table in a stored procedure.
If you create a local temporary table in the stored procedure, the temporary table exists only for the stored procedure. After you exit the stored procedure, the temporary table disappears.
If another stored procedure is called, The called stored procedure can access all objects created by the first stored procedure, including temporary tables.
If you execute a remote stored procedure that changes a remote Microsoft SQL Server 2005 instance, you cannot roll back these changes. Remote stored procedures are not involved in transaction processing.
The maximum number of parameters in stored procedures is 2100.
The maximum number of local variables in a stored procedure is limited by the available memory.
Depending on the available memory, the maximum storage process is 128 MB.

Implement stored procedures

CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number]
[{@ Parameter [type_schema_name.] data_type} [VARYING] [= default] [[OUT [PUT] -- name, type, default value, Direction
[,... N]
[WITH <procedure_option> [,... n]
[For replication]
AS
{<SQL _statement> [;] [... n] | <method_specifier>} -- SQL statement
[;]
<Procedure_option >::=
[ENCRYPTION]
[RECOMPILE] -- compile at runtime
[EXECUTE_AS_Clause]
<SQL _statement >::={ [BEGIN] statements [END]}
<Method_specifier >::= external name assembly_name.class_name.method_name
Execute the Stored Procedure

Use the EXECUTE statement. If the stored procedure is the first statement in batch processing, You can EXECUTE the stored procedure without using the EXECUTE keyword.
Use sp_procoption to enable SQLSERVER to automatically execute the Stored Procedure
Sp_procoption [@ ProcName =] 'processed', [@ OptionName =] 'option ', [@ OptionValue =] 'value' -- the name of the process, the unique value of option is startup, set to true or on, or false or off ).

Use TSQL statements to write stored procedures

I. variables and parameters
The DECLARE statement initializes the Transact-SQL variable using the following operations:
Name. The first character of the name must be @.
Specifies the data type and length provided by the system or defined by the user. Precision and decimal places are also specified for numeric variables. For XML-type variables, you can specify an optional architecture set.
Set the value to NULL.
For example, DECLARE @ MyCounter int
When a variable is declared for the first time, its value is set to NULL. To assign values to variables, use the SET statement. This is the preferred method for assigning values to variables. You can also assign values to variables using the currently referenced values in the SELECT statement selection list.
Parameters are used to exchange data between stored procedures and functions and applications or tools that call stored procedures or functions:
The input parameter allows the caller to pass the data value to the stored procedure or function.
The output parameter allows the stored procedure to pass the data value or cursor variable back to the caller. User-defined functions cannot specify output parameters.
Each stored procedure returns an integer to the caller. If the stored procedure does not explicitly set the return code value, the return code is 0.

2. Process Control statements

1. BEGIN and END statements
The BEGIN and END statements are used to combine multiple Transact-SQL statements into a logical block. The BEGIN and END statements can be used wherever a control flow statement block contains two or more statements.
For example:
IF (@ ERROR <> 0)
BEGIN
SET @ ErrorSaveVariable = @ ERROR
PRINT 'error encountered, '+
CAST (@ ErrorSaveVariable as varchar (10 ))
END
2. GOTO statement
The GOTO statement jumps the execution of the Transact-SQL batch processing to the tag. Do not execute the statement between the GOTO statement and the label.
IF (1 = 1)
GOTO calculate_salary
Print 'go on' -- skip this sentence if the condition is true.
Calculate_salary:
Print 'go'
3. IF... ELSE statement
The IF statement is used for condition testing. The resulting control flow depends on whether an optional ELSE statement is specified:
If (1 = 1)
Print 1
Else if (2 = 2)
Print 2
Else if (3 = 3)
Print 3
Else
Print 0
4. RETURN Statement
The RETURN Statement unconditionally terminates the query, stored procedure, or batch processing. The statements after the RETURN statement in the stored procedure or batch processing are not executed. When a RETURN statement is used in a stored procedure, this statement can specify the integer that is returned to the calling application, batch processing, or process. If RETURN is not specified, the stored procedure returns 0
5. WAITFOR statement
The WAITFOR statement suspends the execution of a batch, stored procedure, or transaction until the following conditions occur:
The specified interval has been exceeded.
The time specified in a day.
The specified RECEIVE statement modifies at least one line or returns it to the Service Broker queue.
The WAITFOR statement is specified by one of the following clauses:
The DELAY keyword is time_to_pass, which indicates the waiting time before the WAITFOR statement is completed. The maximum waiting time before the WAITFOR statement is 24 hours.
For example:
Waitfor delay '00: 00: 02'
SELECT EmployeeID FROM Employee;
The TIME keyword is time_to_execute, which specifies the TIME used to complete the WAITFOR statement.
GO
BEGIN
Waitfor time '22: 00 ';
Dbcc checkalloc;
END;
GO
The RECEIVE statement clause retrieves one or more messages from the Service Broker queue. When WAITFOR is specified using the RECEIVE statement, if no message is displayed currently, this statement waits for the message to arrive in the queue.
The TIMEOUT keyword is timeout, which specifies the length of time (in milliseconds) for the Service Broker to wait for the message to arrive in the queue ). You can specify TIMEOUT in the RECEIVE statement or get conversation group statement.
6. WHILE... BREAK or CONTINUE statement
When the specified condition is True, the WHILE statement repeats the statement or statement block. The REAK or CONTINUE statement is usually used with the WHILE statement. The BREAK statement exits the innermost WHILE loop, and the CONTINUE statement restarts the WHILE loop.
Go
Declare @ Num int
Declare @ ID int
Declare @ I int
Set @ I = 1
While (exists (select * from T where Num <5) -- Obtain records with a quantity less than 5
Begin
Select @ Num = Num, @ ID = ID from T where Num <5 order by ID desc
Print Str (@ I) + 'No.:' + Str (@ ID) + 'value' + str (@ Num)
Update T set Num = Num * 2 where ID = @ ID
Set @ I = @ I + 1
If (@ I> 3)
Break -- exit the loop

End
7. CASE statement
The CASE function is used to calculate multiple conditions and return a single value for each condition. A case function is usually used to replace the code or abbreviation with a more readable value.
-- Usage 1:
Select ID,
Grade = Case Num
When 1 then 'failed'
When 2 then 'failed'
When 3 then 'failed'
When 4 then 'good'
Else 'excellent'
End
From T
--- Usage 2:
Select ID,
Grade = Case
When Num <3 then 'failed'
When Num = 3 then 'pass'
When Num = 4 then 'good'
When Num> 4 then 'excellent'
End
From T

Iii. statements generated during running
You can use either of the following methods to generate SQL statements in TTransact-SQL scripts, stored procedures, and triggers at run time:
Use the sp_executesql stored procedure to execute Unicode strings. Sp_executesql supports parameter replacement similar to the RAISERROR statement.
EXECUTE a string using the EXECUTE statement. The EXECUTE statement does not support parameter replacement in the executed string.
4. Handle Database Engine errors
There are two methods for obtaining error information in Transact-SQL:
1. Within the scope of CATCH blocks constructed by TRY... CATCH, you can use the following system functions:
ERROR_LINE (), returns the wrong row number.
ERROR_MESSAGE (), return the message text that will be returned to the application. This text includes values provided for all replaceable parameters, such as length, object name, or time.
ERROR_NUMBER () returns the error number.
ERROR_PROCEDURE (), returns the name of the stored procedure or trigger with an error. If no error occurs in the stored procedure or trigger, the function returns NULL.
ERROR_SEVERITY () returns the severity.
ERROR_STATE (), return the status.
2. After executing any Transact-SQL statement, you can immediately use the @ ERROR function to test the ERROR and retrieve the ERROR number.
RAISERROR
RAISERROR is used to return messages in the same format as system errors or warning messages generated by SQL Server Database Engine to the application.
3. PRINT
The PRINT statement is used to return messages to the application. PRINT uses a character or Unicode string expression as a parameter and returns the string as a message to the application.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/nutian/archive/2007/10/30/1856313.aspx