SQL server stored procedure application 2: paging query data and dynamic splicing where conditions

Time:2021-5-11

preface

In the development, the query function runs through the full text. We use stored procedure paging query one by one, and support dynamic splicing where condition.

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).

  1. Parameter name needs to be specified;
  2. Specify the parameter data type;
  3. Parameter default value can be specified;
  4. 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

source: https://www.cnblogs.com/xiongze520/p/14667408.html


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.