Detailed Database stored procedures

advantages and disadvantages of stored procedures
Advantages
Faster execution. Stored procedures are compiled only when they are created, and the general SQL statements are compiled every time they are executed, so using stored procedures is faster.
When stored procedures are used to handle complex operations, the program is more readable and the network is less burdensome.
Using stored procedures to encapsulate transactional can be better.
Can be effectively put into the safety of the better.
Maintainability is high, when some business rules change, sometimes only need to adjust the stored procedures, without changing and editing the program.
Better code reuse.
Shortcomings
Stored procedures bring additional pressure to the server.
It is difficult to maintain a lot of stored procedures.
Poor portability, which is more difficult when upgrading to different databases.
Debugging trouble, the SQL language processing function is simple.

In summary, complex operations or SQL recommendations requiring transaction operations use stored procedures, and stored procedures are not recommended for multiple and simple SQL statements.

Stored Procedure definition

A stored procedure is a set of Transact-SQL statements that are compiled only once and can be executed more than once. Because Transact-SQL statements do not need to be recompiled, executing stored procedures can improve performance.
A trigger is a special kind of stored procedure that is not invoked directly by the user. When a trigger is created, it is defined to be fired when a specific type of data modification is made to a particular table or column.

design rules for stored procedures

The CREATE PROCEDURE defines that it can include any number and type of SQL statements, except for the following statements.

You cannot use these statements anywhere in the stored procedure.
Create AGGREGATE, Createrule, create DEFAULT, Createschema, create or alter FUNCTION, create or alter TRIGGER, create, or alter PR Ocedure, 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 stored procedures. You can reference an object that was created in the same stored procedure, as long as the object was created when the reference was made.
Temporary tables can be referenced within a stored procedure.
If you create a local temporary table within a stored procedure, the temporary table exists only for that stored procedure, and the temporary table disappears when you exit the stored procedure.
If the stored procedure that is executed invokes another stored procedure, the invoked stored procedure can access all objects created by the first stored procedure, including temporary tables.
If you perform remote stored procedures that make changes to a remote Microsoft SQL Server 2005 instance, you cannot roll back these changes. Remote stored procedures do not participate in transaction processing.
The maximum number of parameters in a stored procedure is 2100.
The maximum number of local variables in a stored procedure is limited only by available memory.
Stored procedures can be up to 128 MB, depending on the available memory

Implementing Stored Procedures

CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number]
[{@parameter [type_schema_name.] Data_type} [varying] [= default] [[out]]--Name, type, default value, orientation
[,... N]
[With <procedure_option> [,... N]
[For REPLICATION]
As
{<sql_statement> [;] [... n] | <method_specifier>}--sql statement
[;]
<procedure_option>:: =
[Encryption]
[RECOMPILE]-run-time compilation
[Execute_as_clause]
<sql_statement>:: = {[BEGIN] statements [end]}
<method_specifier>:: = Externalname assembly_name.class_name.method_name

Executing stored procedures

Use the Transact-SQL EXECUTE statement. If the stored procedure is the first statement in the batch, the stored procedure can also be executed without using the EXECUTE keyword, which enables SQL Server to automate the stored procedure using sp_procoption
sp_procoption [@ProcName =] ' procedure ', [@OptionName =] ' option ', [@OptionValue =] ' value '--the name of the procedure, the unique value for option is startup, set to turn on (true or on) or off (false or off).

writing stored procedures with TSQL statements

One, variables and parameters
The DECLARE statement Initializes a Transact-SQL variable by doing the following:
Specifies the name. The first character of the name must be an @.
Specifies the system-supplied or user-defined data type and length. Also specify precision and scale for numeric variables. For variables of type XML, you can specify an optional schema collection.
Sets the value to NULL.
such as: DECLARE @MyCounter int
When you declare a variable for the first time, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning values to variables. You can also assign a value to a variable by the value currently referenced in the selection list of the SELECT statement.
Parameters are used to exchange data between stored procedures and functions and applications or tools that call stored procedures or functions:
An input parameter allows the caller to pass data values to a stored procedure or function.
An output parameter allows a stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot specify output parameters.
Each stored procedure returns an integer return code to the caller. If the stored procedure does not explicitly set the value of the return code, the return code is 0.

Second, Process Control statements

1, BEGIN and END statements
The BEGIN and END statements are used to combine multiple Transact-SQL statements into a single logical block. You can use the BEGIN and end statements wherever a control flow statement must execute a block of statements that contains two or more Transact-SQL statements.
Such as:
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 Transact-SQL batches to the label. The statement between the GOTO statement and the label is not executed.
IF (1=1)
GOTO calculate_salary
print ' Go on '--the condition is set to skip this sentence.
Calculate_salary:
print ' Go to '
3, IF ... ELSE statement
The IF statement is used for the test of the condition. 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. Statements that follow the return statement in a stored procedure or batch are not executed. When a return statement is used in a stored procedure, this statement can specify the integer value returned to the calling application, batch, or procedure. If return does not specify a value, the stored procedure returns 0
5, WAITFOR statement
The WAITFOR statement suspends execution of a batch, stored procedure, or transaction until the following occurs:
The specified time interval has been exceeded.
arrives at a specified time of 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:
After the DELAY keyword is time_to_pass, refers to the time that waits before the WAITFOR statement is completed. The maximum amount of time to wait before completing the WAITFOR statement is 24 hours.
Such as:
WAITFOR DELAY ' 00:00:02 '
SELECT EmployeeID from Employee;
The time keyword is followed by Time_to_execute, specifying when the WAITFOR statement is completed.
Go
BEGIN
WAITFOR time ' 22:00 ';
DBCC Checkalloc;
End;
Go
RECEIVE sentence to retrieve one or more messages from the Service Broker queue. When you specify WAITFOR using the RECEIVE statement, if no messages are currently displayed, the statement waits for the message to arrive in the queue.
After the TIMEOUT keyword is TIMEOUT, specify the length of time (in milliseconds) that service Broker waits for the message to arrive in the queue. You can specify TIMEOUT in the RECEIVE statement or the GET Conversation GROUP statement.
6, while ... Break or CONTINUE statement
The while statement repeats a statement or statement block whenever the specified condition is True. Reak or continue statements are commonly used with the while. The break statement exits the most inner 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    )  --fetch records that are 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 loop
   
  end

7. Case statement
The case function is used to evaluate multiple conditions and return a single value for each condition. The case function is often used to replace code or abbreviations with more readable values

--Usage One:
Select ID,
Grade=case Num
When1Then ' failed '
When2Then ' failed '
When3Then ' failed '
When4Then ' good '
Else ' excellent '
End
From T
---usage two:
Select ID,
Grade=case
When num<3Then ' failed '
When num=3Then ' Pass '
When num=4Then ' good '
When num>4Then ' excellent '
End
From T

Three, run-time generation statements

Transact-SQL supports the use of the following two methods to generate a statement in Ttransact-sql scripts, stored procedures, and triggers at run time:
Executes a Unicode string using the sp_executesql system stored procedure. sp_executesql supports parameter substitution similar to the RAISERROR statement.
Executes a string using the EXECUTE statement. The EXECUTE statement does not support parameter substitution in the executed string.

iv. handling Database engine errors
There are two ways to get an error message in Transact-SQL:
1. In TRY ... The scope of the catch block of the catch construct, you can use the following system functions:
Error_line () returns the line number where the error occurred.
Error_message () returns the text of the message that will be returned to the application. The text includes values that are supplied 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 in which the error occurred. If an error does not occur in a stored procedure or trigger, the function returns NULL.
Error_severity () returns the severity.
Error_state (), returns the status.
2. After executing any Transact-SQL statement, you can immediately test the error and retrieve the error number using the @ @ERROR function.
RAISERROR
RAISERROR is used to return a message to the application using the same format for system errors or warning messages generated by SQL Server Database Engine.
3, PRINT
The PRINT statement is used to return messages to the application. PRINT takes a character or Unicode string expression as a parameter and returns the string to the application as a message.