SQL summary-Stored procedures

SQL Summary (v) Stored procedure concepts

Stored procedure (Stored Procedure): One or more SQL statements that have been precompiled as an executable procedure.

Create stored procedure syntax

CREATE proc | procedure Procedure_name    [{@ parameter data type} [= default] [output],     {@ parameter data type} [= default value] [Output],     ....    ]  as     Sql_statementsgo

Comparing stored procedures with SQL statements

Advantage:

1. Improve performance
SQL statements are parsed and compiled when the process is created. The stored procedure is precompiled, and when a stored procedure is first run, the query optimizer analyzes it, optimizes it, and gives the storage plan in the system table that is eventually present, thus saving the overhead when the process is executed.
2. Reduce network Overhead
Stored procedure calls can reduce network traffic by simply providing a stored procedure name and the necessary parameter information.
3. Easy to Code porting
Database professionals can modify stored procedures at any time, but have no impact on the application source code, which greatly improves the portability of the program.
4. Stronger security
1) The system administrator can restrict the execution of a stored procedure to avoid unauthorized user access to the data
2) when calling a procedure over a network, only calls to the execution procedure are visible. As a result, malicious users cannot see table and database object names, embed their own Transact-SQL statements, or search for critical data.
3) Use process parameters to help avoid SQL injection attacks. Because parameter inputs are treated as literal values rather than executable code, it is more difficult for an attacker to insert a command into a Transact-SQL statement within a procedure and compromise security.
4) The process can be encrypted, which helps to obfuscate the source code.

Disadvantage:

1, the stored procedure needs the specialized database developer to carry on the maintenance, but the actual situation is, often by the program developer personnel part-time

2. Design logic change, modify stored procedure without SQL flexible

Why is there a relatively small number of stored procedures used in real-world applications?

In the usual project development, the use of stored procedures is relatively small, which is why?
The reasons for this analysis are:
1) No specific database developer, regular programmer part-time database operation
2) programmers often only need to operate the program, can complete data access, no need to develop on the database
3) The change of the project demand is more frequent, it is convenient to modify the SQL statement, especially the logic change

how are stored procedures and SQL statements decided?

Based on practical application experience, the following recommendations are given:

1, in some high-efficiency or more normative requirements of the project, recommend the use of stored procedures
2, for general project recommendations using parameterized Command mode, is a storage process and SQL statement a compromise way
3, for some of the algorithm requirements are relatively high, involving multiple data logic, the proposed use of stored procedures

Specific application of stored procedures

First, the basic query

1. Create a stored procedure with no parameters

Example: Querying the total number of students

--Query stored procedure if OBJECT_ID (n ' proc_select_students_count ', n ' P ') is not NULL    DROP procedure Proc_select_students_count; Gocreate procedure Proc_select_students_countas     SELECT COUNT (ID) from Studentsgo

Perform:

EXEC Proc_select_students_count

2. Stored Procedures with parameters

-- Query the stored procedure, based on the total number of city queries if OBJECT_ID (n'proc_select_students_by_city_count', n'  P') is not NULL    DROP procedure Proc_select_students_by_city_count; Gocreate procedure Proc_select_students_by_city_count (@city nvarchar) as    SELECT COUNT ( ID) from Students WHEREcity =@cityGO

Perform:

EXEC proc_select_students_by_city_count N'Beijing'

3, with wildcard wildcard, with the corresponding wildcard copy code when assigning values to a parameter value--3, query student information for surname Li, including wildcard if OBJECT_ID (N'Proc_select_students_by_surnname'N'P') is not NULL DROP procedure proc_select_students_by_surnname; Gocreate procedure proc_select_students_by_surnname @surnName nvarchar ( -)='Li%'--default value as SELECT id,name,age from Students WHERE Name is like @surnNameGO copy code execution: EXEC proc_select_students_by_surnnameexec Proc_select_students_by_surnname N'Li%'EXEC proc_select_students_by_surnname N'% li%' 4, with output parameter copy code--Student information by name, return to student's city and age if object_id (N'Proc_select_students_by_name'N'P') is not NULL DROP procedure proc_select_students_by_name; Gocreate procedure proc_select_students_by_name @name nvarchar ( -),     --input parameters @city nvarchar ( -) out, --output parameter @ageintOutput--input and output parameters as SELECT @city=city, @age =age from Students WHERE [email protected] and age=@ageGO Copy Code execution: copying code--perform declare @name nvarchar ( -), @city nvarchar ( -), @ageint;Set@name = N'Li Ming';Set@age = -; exec proc_select_students_by_name @name, @city out, @age output;Select@city, @age; Copy code ii. using stored procedures for pruning1, add new Student information copy code--1, stored procedures: New Student information if OBJECT_ID (N'proc_insert_student'N'P') is not NULL DROP procedure proc_insert_student; Gocreate procedure Proc_insert_student @idint, @name nvarchar ( -), @ageint, @city nvarchar ( -as INSERT into Students (id,name,age,city) VALUES (@id, @name, @age, @city) Go copy code execution: EXEC proc_insert_student1001N'Zhang San', +,'Shanghai'2, modify the student information according to the student ID, copy the code if OBJECT_ID (N'proc_update_student'N'P') is not NULL DROP procedure proc_update_student; Gocreate procedure Proc_update_student @idint, @name nvarchar ( -), @ageint, @city nvarchar ( -) as UPDATE Students SET Name[Email protected],[email protected],[email protected] WHERE id=@idGO Copy Code execution: EXEC proc_update_student1001N'Zhang Sizhong's', -,'Shanghai' 3Delete A student record copy code based on ID--3, stored procedure: Delete Student information if object_id (N'proc_delete_student_by_id'N'P') is not NULL DROP procedure proc_delete_student_by_id; Gocreate procedure proc_delete_student_by_id @idintAs DELETE from Students WHERE ID=@idGO Copy Code execution: EXEC proc_delete_student_by_id1001third, the stored procedure realizes the paging query1, using the Row_number function to page copy code--paged Query if OBJECT_ID (N'Proc_select_by_page'N'P') is not NULL DROP procedure proc_select_by_page; Gocreate procedure Proc_select_by_page @startIndexint, @endIndexintAs SELECT*From (SELECT id,name,age,city,row_number () Up (ORDER by ID DESC) as RowNumber from Students) as temp WHERE temp. RowNumber between @startIndex and @endIndexGO copy code execution: EXEC proc_select_by_page1,Ten2, using traditional top paging to copy code--Use top page if object_id (N'Proc_select_by_page_with_top'N'P') is not NULL DROP procedure proc_select_by_page_with_top; Gocreate procedure Proc_select_by_page_with_top @pageIndexint, @pageSizeintAs SELECT TOP (@pageSize)*From Students WHERE ID>= (select MAX (ID) from (select TOP (@pageSize * (@pageIndex-1) +1) ID from Students ORDER by ID) as Temp) Go copy code execution: EXEC proc_select_by_page_with_top1,2Iv. Other functions:1, stored procedures, and recompilation of replication code for each execution--1, stored procedures, repeatedly compiling if object_id (N'Proc_select_students_with_recompile'N'P') is not NULL DROP procedure proc_select_students_with_recompile; Gocreate procedure Proc_select_students_with_recompilewith Recompile--repeatedly compiling as SELECT*From studentsgo Copy code2, the source script copy code cannot be viewed and modified after the stored procedure is encrypted--2, querying stored procedures, encrypting if OBJECT_ID (N'proc_select_students_with_encryption'N'P') is not NULL DROP procedure proc_select_students_with_encryption; Gocreate Procedure Proc_select_students_with_encryptionwith Encryption--Encrypt as SELECT*From studentsgo copy code execution: EXEC proc_select_students_with_encryption effect, unable to view script or export create script

SQL summary-Stored procedures