First, the concept of stored procedures, advantages, syntax
Before writing a note, the first thing you need to do is to sort out the conceptual stuff, otherwise it will create a sense of strangeness or confusion.
Concept: will be commonly used or very complex work, written in advance using SQL statements and stored with a specified name, then if you call these SQL statements, you only need to use Execute/exec to execute the following.
Pros: Of course, there are many advantages to using stored procedures, as explained in one by one below.
1, the stored procedure is only in the creation of the time to compile, each time the execution, you do not need to compile, but the direct use of SQL, you need to compile every time, so use stored procedures can improve the execution speed of the database.
2, when the database for complex operations, the use of stored procedures for encapsulation, can reduce the probability of error code, and MSSQL itself has the ability to debug code, can easily locate the error statement.
3, stored procedures can be reused, can improve the development efficiency of developers.
4, high security, you can set only specific permissions of the user to operate the stored procedures, but also to a certain extent to prevent SQL injection operations.
Category: Stored procedures are divided into three categories, namely system stored procedures, extended stored procedures, user-defined stored procedures.
1, the system stored procedures: is to sp_ the beginning of the stored procedures, used to carry out a variety of system settings, access to information, to carry out related management work, such as: sp_help is to get the relevant information of the specified object.
2, extended stored procedures: is the beginning of the xp_, used to invoke the functions provided by the operating system. The following is a reference: exec master. xp_cmdshell ' ping 127.0.0.1 '
3. User-defined stored procedures:
Common formats are as follows:
Copy Code code as follows:
Create procedure Procedue_name
[@parameter Data_type] [Output]
[With] {Recompile|encryption}
As
Sql_statement
What needs to be explained is:
Output: Indicates that this parameter is capable of being returned.
[With] Recompile in {Recompile|encryption}: Indicates that each time the stored procedure is executed, it is recompiled (by default, it is compiled only when it is created).
Encryption: The contents of the stored procedure created are encrypted.
Tip: Here's how to find the names of all the columns of a table that contains a keyword in a database if we sometimes want to find them. You can use the following statement:
Copy Code code as follows:
Select Table_name,column_name from INFORMATION_SCHEMA. Columnswhere column_name like '%a% '; --View those tables that contain columns with a
But if you want to find the name of a stored procedure that has a table "B" in the stored procedure, you can do so by using the following statement:
Copy Code code as follows:
Select Routine_name, routine_definition from Information_schema.routines
Where routine_definition like '%b% '
and routine_type= ' procedure '
Of course, we can actually use the syscomments,sysobjects,sysdepends in SQL to view specific data information, which is similar to the dba_objects in Oracle:
Copy Code code as follows:
SELECT * from syscomments; --View Callout
select * from sysobjects; --View Database objects
SELECT * from sysdepends; --View Dependencies
Second, the storage process advanced
Of course, let's talk about the formatting syntax rules for the stored procedure:
Copy Code code as follows:
Create Procedure procedure-name (Input parameters, Output parameters (If required)) Asbegin SQL statement used in the STO Red Procedureend
Here we use a common example to illustrate:
Copy Code code as follows:
/* Getstudentname is the name of the stored procedure*/
Create PROCEDURE Getstudentname (
@studentid INT--input parameter, StudentID of the student
)
As
BEGIN
SELECT firstname+ ' +lastname from tbl_students WHERE studentid= @studentid
End
Of course, the @studentid parameter here is just an incoming parameter, but if you want to return a value, then you need to use the out parameter to implement, the specific implementation code is as follows:
Copy Code code as follows:
/*
Getstudentnameinoutputvariable is the name of the stored procedure which
Uses output variable @Studentname to collect the student name returns by the
Stored Procedure
*/
Create PROCEDURE getstudentnameinoutputvariable
(
@studentid INT,--input parameter, StudentID of the student
@studentname VARCHAR-out parameter declared and out keyword
)
As
BEGIN
SELECT @studentname = firstname+ ' +lastname from tbl_students WHERE studentid= @studentid
End
From the above code, you can see the specific use of out parameters, but if you want to execute this code on the SQL Server side, how do you do that?
In fact, when it comes to this, a little trouble, if it is only in parameters, then only need to use the execute/exec followed by the name of the stored procedure, which can be assigned to the parameters, but if not only in the parameters, but also have out parameters, how to do this?
The following is a specific example to describe the usage in detail:
Copy Code code as follows:
Alter PROCEDURE getstudentnameinoutputvariable
(
@studentid INT,--input parameter, StudentID of the student
@studentname VARCHAR,--Output parameter to collect the student name
@StudentEmail VARCHAR--Output Parameter to collect the student email
)
As
BEGIN
SELECT @studentname = firstname+ ' +lastname,
@StudentEmail =email from tbl_students WHERE studentid= @studentid
End
You can see that the above stored procedure has three parameters, where the first is in parameter, and then two are out parameters, from the process body can see that the first out parameter is to get the full name of the student, the second is to get email.
So how do you view the results from the server side?
Copy Code code as follows:
Declare @Studentname as nvarchar (200)--Declare the first output parameter
Declare @Studentemail as nvarchar (50)--Declare the second output parameter
Execute getstudentnameinoutputvariable 1, @Studentname output, @Studentemail output
Select @Studentname, @Studentemail--"Select" Statement to view results
Well, the above is some knowledge of the stored procedures, subsequent articles will be cursors, custom errors, things run to include in the hope that SQL in step by step learning, solid growth.
