Stored procedure basic knowledge of stored procedures

Stored procedures are the necessary technology to do the project, as long as you interview to find work, database and stored procedures are also required, the following to understand the simple basic technical knowledge of stored procedures:
First, the basic concept of stored procedures.
A stored procedure is one or more SQL commands that are stored as executable objects in the database. In layman's terms: a stored procedure is actually a set of SQL statements that can be done in a certain way, a block of code that consists of a number of T-SQL statements that implement functions like a method (check for additions or deletions to a single table or multiple tables), and then give the code block a name, Call him when you're using this feature.


Second, why use stored procedures.
1. Stored procedures are compiled only when created, and each time a stored procedure is executed without recompiling, and the general SQL statements are compiled every time, so using stored procedures can improve database execution speed.
2. When complex operations are performed on a database, this complex operation can be encapsulated in a stored procedure and used in conjunction with transaction processing provided by the database.
3. Stored procedures can be reused to reduce the workload of database developers.
4. High security, you can set only some users have the right to use the specified stored procedures


Three, create the parameters of the stored procedure:
1.procedure_name: The name of the stored procedure in front Plus # is a local temporary stored procedure, plus # #为全局临时存储过程.


2.number: An optional integer that is used to group procedures with the same name so that a drop PROCEDURE statement can be used to remove the same group of procedures. For example, an application named orders uses a procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement drops the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, and the appropriate delimiter should be used only before and after procedure_name.


3. @parameter: Parameters for Stored procedures. can have one or more. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined). A stored procedure can have up to 2.1 parameters.
Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, parameters can only be substituted for constants and cannot be used in place of a table name, column name, or other database object name. For more information, see EXECUTE.


4.data_type: The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used for output parameters. If you specify a data type of cursor, you must also specify both varying and OUTPUT keywords. For more information about the data types and their syntax provided by SQL Server, see Data types.
Indicates that there is no maximum number of restrictions for output parameters that can be cursor data types.


5.varying: Specifies the result set that is supported as output parameters (dynamically constructed by stored procedures and content can be changed). Only cursor parameters are applicable.


6.default: The default value for the parameter. If you define a default value, you do not have to specify the value of the parameter to execute the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for this parameter, the default value can contain wildcard characters (%, _, [], and [^]).


7.output: Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. Output parameters that use the input keyword can be cursor placeholders.


8.recompile: Indicates that SQL Server does not cache the schedule for this procedure, which will be recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and do not want to overwrite execution plans that are cached in memory.


9.encryption: An entry that contains the text of the CREATE PROCEDURE statement in the SQL Server encryption syscomments table. Use encryption to prevent the process from being published as part of SQL Server replication. Description during the upgrade process, SQL Server uses the encrypted annotations stored in syscomments to recreate the encryption process.


10.for replication: Specifies that stored procedures created for replication cannot be executed at the Subscriber. Stored procedures created using the FOR REPLICATION option can be used as stored procedure filtering and can only be performed during the replication process. This option cannot be used with the WITH RECOMPILE option.


11.as: Specifies the action to be performed by the procedure.


12.sql_statement: Any number and type of Transact-SQL statements to include in the procedure. But there are some restrictions.




Iv. How to use stored procedures.
The following table student is used to understand stored procedures:

No parameter stored procedure:
Select all the information in the student table,

Create proc Studentproc
As--here as can not omit not write
Begin--begin and end are a pair, you can not write only one, but you can not write
Select Id,name,age,sex from Student
End
Go


There are parameter stored procedures:
Global variables
A global variable, also called an external variable, is defined outside the function, and its scope starts at the variable definition and ends at the end of the program file.
Select student information for the specified name:


Create proc Studentproc
@name varchar (50)
As
Begin
Select Id,name,age,sex from student where Name= @name
End
Go


exec studentproc ' John '--EXECUTE statement


The above is to assign a value to the variable externally, or you can set the default value directly to the variable internally


Create proc Studentproc
@name varchar (50) = ' John '
As
Begin
Select Id,name,age,sex from student where Name= @name
End
Go
EXEC Studentproc


You can also output the content of the variable using output


Create proc Studentproc
@name varchar (50),
@IsRight int output--Outgoing parameters
As
If exists (select Id,name,age,sex from student where Name= @name)
Set @IsRight =1
Else
Set @IsRight =0
Go


DECLARE @IsRight int
exec studentproc ' John ', @IsRight output
Select @IsRight


The above is a global variable, and here's how to understand local variables
Local variables are also called internal variables. A local variable is defined within a function. Its scope is limited to the inside of the function, it is illegal to leave the function and then use the variable.
Definition of local variable: You must first set the DECLARE command to use the declare{@ variable name data type}
Assignment method for local variables: set{@ variable name = expression} or select{@ variable name = expression}
Display of local variables: SELECT @ variable Name


Create proc Studentproc
As
DECLARE @name varchar (50)
Set @name = ' John '
Select Id,name,age,sex from student where Name= @name
Go


EXEC Studentproc


What if you want to show the data of local variables?


Create proc Studentproc
As
DECLARE @name varchar (50)
Set @sname = (select name from student where id=1)
Select @name
Go


EXEC Studentproc