SQL Syntax advanced application five: Using stored procedures to achieve detailed multi-level statistics

Objective

In the previous section we talked about the basic use of stored procedures, and this chapter will take a scenario that is actually applied in the project.

There are often such requirements in the project, such as the need for a list of details by group, by level, by person, and so on, if the query conditions with the list, how to implement it?

The general idea is: get the detail data first, then according to the group, the level, the person carries on the statistic to obtain three sets.

The right thing to do is to get a list of the current dimensions by what the customer needs, and avoid the logic processing that is not used in the program as much as possible.

Here is my solution:

Scheme

My plan is to use stored procedures to pass parameters of the query and statistical dimensions to the stored procedure, and the stored procedure is responsible for returning the statistical results.

The following is a practical case:

Stored Procedure Code

 Usewot_inventory;GO--determine if the stored procedure name to be created existsIF EXISTS(    SELECT *     fromdbo.sysobjectsWHEREId= object_id(N'[Wot_inventory]. [dbo]. [Sp_nodeliverystatistics]')           and ObjectProperty(ID, N'isprocedure')= 1)    --To Delete a stored procedure    DROP PROC [Sp_nodeliverystatistics];GOCREATE PROC [Sp_nodeliverystatistics](    @ListName NVARCHAR( -),--List name    @Nav NVARCHAR( -),--Navigation name    @Where VARCHAR(MAX),--Query Criteria    @msg NVARCHAR( $) OUTPUT) asBEGIN    SETNOCOUNT on; DECLARE @sqlStr NVARCHAR(MAX); BEGINTRYIF @ListName <> "'            and @ListName  is  not NULL            and @Nav <> "'        BEGIN            IF(@ListName = 'agreed')            BEGIN                SET @sqlStr                    =N'SELECT' + @Nav                      + ', COUNT (Id) number,sum (receivable) totalreceivable,avg (receivable) avgreceivable,sum (agencyfund) Totalagencyfund,avg (receivable) Avgagencyfund from Wot_Inventory.dbo.NoDeliveryByDetailView WHERE 1 = 1'; EXEC(@sqlStr + @Where +N'GROUP by' + @Nav + "'); SET @msg = 'List Statistics Successful'; END; ELSE IF(@ListName = 'Immediate')            BEGIN                SET @sqlStr                    =N'SELECT' + @Nav                      + ', COUNT (Id) number,sum (receivable) totalreceivable,sum (agencyfund) totalagencyfund,sum (Insurrevalue) Totalinsurrevalue,sum (Fragileinsurrevalue) Totalfragilevalue from Wot_Inventory.dbo.NoDeliveryByDetailView WHERE 1 = 1'; EXEC(@sqlStr + @Where +N'GROUP by' + @Nav + "'); SET @msg = 'List Statistics Successful'; END; ELSE            BEGIN                SET @msg = 'the passed parameter is not correct'; END; END; ELSE        BEGIN            SET @msg = 'the passed parameter is not correct'; END; ENDTRYBEGINCATCHSET @msg =error_message (); PRINT @msg; ENDCATCH; PRINT @msg;END;GO

stored Procedure SQL

Program code

List<agreedstatistics> DTORESULT1 =NewList<agreedstatistics>(); List<AgreedStatistics> DTORESULT2 =NewList<agreedstatistics>(); List<AgreedStatistics> DTORESULT3 =NewList<agreedstatistics>(); List<AgreedStatistics> DTORESULT4 =NewList<agreedstatistics>(); List<AgreedStatistics> DTORESULT5 =NewList<agreedstatistics>(); //by Appointment            stringsql ="EXEC Wot_Inventory.dbo.sp_nodeliverystatistics @ListName, @Nav, @Where, @msg out"; varSqlwhere =""; Sqlwhere= Setsqlwhere (sqlwhere, request, user,"Dtl"); Sqlparameter[] Parms=Newsqlparameter[] {NewSqlParameter ("@ListName","agreed"),                NewSqlParameter ("@Nav","agreeddate"),                NewSqlParameter ("@Where", Sqlwhere),NewSqlParameter ("@msg", System.Data.SqlDbType.NVarChar, $)            }; parms[3]. Direction =System.Data.ParameterDirection.Output; DTORESULT1= deliverrepository.executesql<agreedstatistics> (sql, parms). OrderByDescending (t =t.totalreceivable).            ToList (); Msg= parms[3].            Value.tostring (); Dtoresult1.foreach (t={t.id= index1++;            }); //Sales Group            stringSQL2 ="EXEC Wot_Inventory.dbo.sp_nodeliverystatistics @ListName, @Nav, @Where, @msg out"; varSqlwhere2 =""; Sqlwhere2= Setsqlwhere (sqlwhere2, request, user,"Dtl"); Sqlparameter[] Parms2=Newsqlparameter[] {NewSqlParameter ("@ListName","agreed"),                NewSqlParameter ("@Nav","Salesgroupname"),                NewSqlParameter ("@Where", Sqlwhere2),NewSqlParameter ("@msg", System.Data.SqlDbType.NVarChar, $)            }; parms2[3]. Direction =System.Data.ParameterDirection.Output; DTORESULT2= Deliverrepository.executesql<agreedstatistics> (SQL2, PARMS2). OrderByDescending (t =t.totalreceivable).            ToList (); Msg= parms2[3].            Value.tostring (); Dtoresult2.foreach (t={t.id= index2++; });

Background Code

Front End effect

In the current case, I used the cache to cache the statistics of all the dimensions while querying the details, because the list of details does not produce too much data. This is also a problem-solving thinking, depending on the actual situation.

PS: Welcome to scan the QR code below or click on the link, join QQ group

SQL Syntax advanced application five: Using stored procedures to achieve detailed multi-level statistics