Key points:Support dynamic splicing where condition
Students who have questions about the use of stored procedures【Creation and use of SQL server stored procedure】Make up lessons.
As for whether you use custom SQL query or related ORM framework query, we will not discuss. Let’s briefly introduce stored procedure query (custom SQL query).
Create a stored procedure and execute it
The flow chart is as follows. We implement the code according to the flow chart.
1. Creating a stored procedure with parameters
To create a stored procedure with parameters, the first step is to declare the parameters in the stored procedure, and each stored procedure parameter must be defined with a unique name.
As with the standard transact SQL variable, the parameter name must be prefixed with @ and comply with the object identifier rule.
When the user does not provide a value for this parameter, a default value can be used instead.
When executing a stored procedure with parameters, you can either explicitly specify the parameter name and assign an appropriate value, or you can pass the value to the stored procedure by providing the parameter value given in the create procedure statement (without specifying the parameter name).
In stored procedure Pro_ Student_ In, four parameters are named in the order of @ Chinese, @ English, @ math and @ class.
For example, pass a value to the parameter name specified by the stored procedure.
EXEC PRO_ Student_ IN @class=" One class every three [email protected] Chinese=85,@maths=85 ,@English=85
For example, pass by the location of the parameter without naming the parameter name.
EXEC PRO_ Student_ IN 85,85,85," One class every three years;
2. Define a parameter to accept the spliced SQL statement
By specifying procedure parameters, the calling program can pass values to the body of the procedure.
If the parameter is marked as Output parameter, the procedure parameter can also return the value to the calling program.
A process can have up to 2100 parameters, each with a name, data type, and direction. You can also specify a default value for the parameter (optional).
The parameter value provided by using procedure call must be constant or variable. Function name cannot be used as parameter value. A variable can be a user-defined variable or a system variable (for example, @ @ SPID).
- Parameter name needs to be specified;
- Specify the parameter data type;
- Parameter default value can be specified;
- You can specify the parameter mode (the default is the input parameter).
3. Create a temporary table to store the result set after query splicing condition
The temporary table is similar to the permanent table, but the temporary table is stored in tempdb and will be automatically deleted when it is no longer used. There are two types of temporary tables: local and global. They differ in name, visibility, and usability.
The temporary table has the following characteristics:
- Local temporary table is a table that users add “#” prefix when creating a table, which is independent according to the database connection. Only the database connection that creates the local temporary table has access to the table, and other connections cannot access the table;
- In different database connections, although the local temporary tables created have the same name, there is no relationship between these tables;
- In SQL server, a special naming mechanism is used to ensure the independence of local temporary table in database connection.
The real temporary table uses the temporary table space of the database, which is maintained automatically by the database system, so the table space is saved. And because the temporary table space generally uses virtual memory, it greatly reduces the number of I / O of the hard disk, so it also improves the efficiency of the system.
The temporary table will be cleared automatically after the transaction or session, and you don’t have to remember to delete the data after it is used up.
Local temporary table
- The name of the local temporary table starts with a single numeric symbol (#);
- They are only visible to the current user connection (that is, the connection that creates the local temporary table);
- Is deleted when the user is disconnected from the SQL server instance.
4. The paging query returns the final SQL statement and the total number of rows, the query conditions add the data of the temporary table, and finally delete the temporary table
SQL Server database paging query has always been a short board of SQL server. There are several paging methods. Suppose there are table article, field ID, year… (others omitted),
There are 53210 pieces of data (real customer data, small amount), 30 pieces per page, 1500 pages (45001-45030 pieces of data), field ID clustering index, year no index.
The first optionThe simplest and most common method:
SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC
Average time for 100 queries: 45s
The second option is to:
SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC
Average time for 100 queries: 138s
The third scheme is as follows:
SELECT * FROM ARTICLE w1, ( SELECT TOP 30 ID FROM ( SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC ) w ORDER BY w.YEAR ASC, w.ID ASC ) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC
Average time for 100 queries: 21s
The fourth scheme:
SELECT * FROM ARTICLE w1 WHERE ID in ( SELECT top 30 ID FROM ( SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC ) w ORDER BY w.YEAR ASC, w.ID ASC ) ORDER BY w1.YEAR DESC, w1.ID DESC
Average time for 100 queries: 20s
The fifth scheme:
SELECT w2.n, w1.* FROM ARTICLE w1, ( SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC
Average time for 100 queries: 15s
Complete stored procedure code
/**Author: Xiong Ze date: 2021-04-16 project: sqlserver stored procedure application 2: paging query data and dynamic splicing where conditions*/ --Create a stored procedure to query students CREATE PROCEDURE ProcedureStudent @PageIndex int, - current page (e.g. 1: Page 1) @Pagecount int, - number of entries per page (e.g. 50 entries per page) @Rowtotal int output, - the total number of rows returned @Strwhere varchar (5000) -- SQL query conditions for program dynamic splicing AS BEGIN /**Begin to create dynamic conditions for splicing*/ DECLARE @sq_ Temp as varchar (2000) -- define the SQL statement after splicing Create table # temp (- - creates a splicing query condition, and the result of the query is used for sub query Number VARCHAR(50) ) SET @sq_temp ='SELECT Number FROM a_Students where 1 = 1 ' +Case when isnull (@ strwhere, ') =' then 'else @ strwhere end -- the condition of dynamic splicing --Write the spliced condition to the temporary table INSERT INTO #temp (Number) EXEC (@sq_temp); /**End creating dynamic conditions for stitching*/ --Paging query SQL SELECT * FROM (SELECT row_number()over (order by a.Number desc)Id, a. Number, a. Name, b. Classname class, c.Java , c.Python , c.C# , c.SqlDB FROM a_Students a LEFT JOIN a_StudentClass b ON a.ClassId = b.ClassId LEFT JOIN a_StudentsScore c ON a.Number = c.Number WHERE a.Number IN (SELECT Number FROM #temp) )temp WHERE temp.Id between (@pageIndex-1)*@pageCount+1 and @pageIndex*@pageCount; --Return the total number SELECT @rowTotal=COUNT(*) FROM dbo.a_Students WHERE Number IN (SELECT Number FROM #temp) Drop table # temp -- delete temporary table END GO
Calling stored procedure
1. There is no dynamic splicing where condition
--There is no where condition for calling paged stored procedures DECLARE @total INT EXEC ProcedureStudent 1,5,@total OUT,'' Total number of total rows returned by select @ total
2. Where condition with dynamic splicing
--Call paging stored procedure, where condition: data with student number 100014 DECLARE @total INT EXEC ProcedureStudent 1,5,@total Out, 'and number' ='100014 '' -- Dynamic splicing condition: data with student number of 100014 Total number of total rows returned by select @ total
That’s all. Bye.
Welcome to subscribe to WeChat public official account.
Author: Xiong Ze – pain and joy in learning
Official account: Bear’s saying
It’s not easy to create. If anyone, organization or organization reprints or extracts all or part of it, please indicate the author and the link of the original text in the obvious position of the article.