Storage Procedure Learning Record 1 My first stored procedure The function of the stored procedure is to complete the joint query and calculation in multiple non-relational data sheets, and finally generate a statistical table. This stored procedure includes: parameter delivery, mutual calls and returns the result set to the temporary table, using sp_executesql to perform dynamically generated SQL statements, creation of the temporary table and use =========== ============================================================================================================================================================================================================= = CREATE Procedure sp_makecond @fdname nvarchar (20), @fdrecord nvarchar (250), @cxzt int, @gsbm nvarchar (10), @cond nvarchar (250) Outputas
/ * Function: According to the @cxzt status, the content @fdname matches the field @fdname. When there is content in @fdrecord, it is matched when there is content in @GSBM, and adds GSBM condition input: @fdname field name @FDRecord field content @GSBM @cxzt query status 0- Expressive, 1- indicates blur, 2 - Indicates that the smart is only used to play only the KHMC: @Cond condition statement, for example: enter DWMC, 'Shanghai', 1, 'RRL' Output DWMC Like 'Shanghai%' and GSBM = 'RRL' * / / * Creation : Creating Date: - Update records in this place for the version of the process
* / Declare @havefdrecord as int
Set @havefdrecord = 0 set @cond = ''
/ * Add fdrecord * / if len (RTRIM (@fdrecord))> 0 begin select @ Cond = Case @cxzt when 0 Then @cond @FDNAME N '=' ' @fdrecord ' '' 'When 1 Then @cond @fdname n' Like '' ' @fdrecord '% '' 'When 2 Then @cond @fdname n' Like ''% ' @fdrecord '% '' ' End set @havefdrecord = 1 end
/ * Add GSBM * / if Len (RTRIM (Ltrim (@GSBM)))> 0 Begin if @ HavefDRecord = 1 Begin Set @cond = @cond 'and' end set @cond = @cond 'GSBM =' ' ' @GSBM ' '' endgo ========================================= ====================== Create Procedure sp_math_khye @Khmc nvarchar (250), @JZRQ DateTime, @gsbm nvarchar (10) as / * function : Enter: @KHMC Customer Name @cxzt Query Status 0- Expressive, 1-Representation Blur, 2- Represents Smart This Status Only KHMC Up to Date @GSBM Output: @ReturnkHye Customer Balance Calculated by Conditions : Before calling the process, you need to define a temporary table name to #khyreturn (DWMC NVARCHAR (250), Khye Decimal (15, 6)) after using the following statement, after using #KHYereturn, should immediately use the following Statement Release This Temporary Table Delete From #khyreturn Drop Table #khyreturn * // * Implementation Method: * /
/ * Created: Create Date:
- Update records for retrofitting store procedures in this place
* / Declare @sqlstring nvarchar (1000) Declare @cond nvarchar (250)
- Making query conditions, deposit @cond execute sp_makecond 'dwmc', @ khmc, @ cxzt, @ gsbm, @ Cond output / * * / set @sqlstring = 'SELECT DWMC, SUM (XHSL * HSJ) AS Xsje from xsdda Where xsrq <= @ vjzrq and ' @cond set @sqlstring = @sqlstring ' Group by dwmc '
Create table #sp_Math_KHYE_XSJE (dwmc nvarchar (250), xsje decimal (15,6)) Declare @ParmDefinition nvarchar (100) set @ParmDefinition = '@vKHMC nvarchar (250), @ vJZRQ datetime' - this is the place to call sp_executesql Perform dynamic sqlstring and return the result to a temporary table - here, I have tried to return the result to the middle of the cursor, but there is no success. - If the information INSERT and EXEC are unacceptable, but it is quite strange in this place, and these two can be embedded. - Need to note: @ParmDefnition is used to define the variables to be used in sp_executesql. - Test this place directly to pass a string, do not use variable delivery, the result is wrong. I don't know if there is any other person to succeed - I will pass a variable list after @ParmDefinition, follow my understanding, - is assigned to the variables you want to use during the sp_executesql process (which is the variable of SQL statements) insert into #sp_Math_KHYE_XSJE EXECUTE sp_executesql @SqlString, @ParmDefinition, @ vKHMC = @ KHMC, @ vJZRQ = @ JZRQ / * * / set @SqlString = '' set @SqlString = 'select dwmc, sum (bcsk ysk) as fkje from XHSKDDA WHERE SKRQ <= @ vjzrq and ' @cond set @sqlstring = @sqlstring ' Group by dwmc '
Create table #sp_Math_KHYE_FKJE (dwmc nvarchar (250), fkje decimal (15,6)) set @ ParmDefinition = '' set @ParmDefinition = '@vKHMC nvarchar (250), @ vJZRQ datetime' insert into #sp_Math_KHYE_FKJE EXECUTE sp_executesql @SqlString, @Parmdefinition, @ vkhmc = @ khmc, @ vjzrq = @ JZRQ Create Table #sp_math_khye_result (dwmc nvarchar (250), Khye Decimal (15, 6)) - relatively simple SQL statement, left-line right, BT needs , Can only use BT to write
insert into #sp_Math_KHYE_Result select distinct T1.dwmc as dwmc, (T1.fkje-T1.xsje) as khye from (select # sp_Math_KHYE_XSJE.dwmc as dwmc, isnull (# sp_Math_KHYE_XSJE.xsje, 0) as xsje, isnull (#sp_Math_KHYE_FKJE. fkje, 0) as fkje from # sp_Math_KHYE_XSJE, # sp_Math_KHYE_FKJE where # sp_Math_KHYE_XSJE.dwmc * = # sp_Math_KHYE_FKJE.dwmc union select # sp_Math_KHYE_XSJE.dwmc as dwmc, isnull (# sp_Math_KHYE_XSJE.xsje, 0) as xsje, isnull (# sp_Math_KHYE_FKJE.fkje , 0) as fkje from # sp_Math_KHYE_XSJE, # sp_Math_KHYE_FKJE where # sp_Math_KHYE_FKJE.dwmc * = # sp_Math_KHYE_XSJE.dwmc) as T1insert into #KHYEReturn select * from #sp_Math_KHYE_ResultGO ================= =========================================================================================================================================== Create Procedure sp_khyexytj @StartDate DateTime, @enddate datetime, @Khmc nvarchar (250), @cxzt int, @Gsbm nvarchar (100) AS / * Function: Enter: @startdate Start Date @Enddate As of Date @Khmc Customer Name @cxzt Query Status 0- Expressive, 1-Representation Blur, 2- Represents the intelligent status only to KHMC @GSBM output: Table structure: dwmc ncye ssye bqxs qmye khxye cxye * / / * creation People: Create Date:
- Update records for retrofitting store procedures in this place
* / - This place seeks a pile of chaos and seven-eight-piece data, it is necessary to use it.
- determined Table #sp_KHYEXYTJ_QMKHYE Create Table #sp_KHYEXYTJ_QMKHYE (dwmc nvarchar (250), QMkhye decimal (15,6)) Create Table #KHYEReturn (dwmc nvarchar (250), khye decimal (15,6)) execute sp_Math_KHYE @KHMC, @ EndDate, @ CXZT, @ GSBM insert into #sp_KHYEXYTJ_QMKHYE (dwmc, QMKHYE) select * from #KHYEReturn delete from # KHYEReturn-- obtained table #sp_KHYEXYTJ_QMKHYE Declare @NowDate datetime set @NowDate = GetDate () Create table #sp_KHYEXYTJ_KHYE (dwmc NVARCHAR (250), KHYE DECIMAL (15, 6)) EXECUTE SP_MATH_KHYE @ khmc, @ NowDate, @ cxzt, @ gsbm INSERT INTO #SP_KHYEXYTJ_KHYE Select * from #khyereturn delete from #Khyereturn
- Survey table #sp_khyexytj_ncye create table #sp_khyexytj_ncye (dwmc nvarchar (250), NCYE DECIMAL (15, 6)) set @ Nowdate = Cast (Str (year (getdate ())) '- 01-01 00:00 : 00 'as datetime) EXECUTE SP_MATH_KHYE @ khmc, @ NowDate, @ cxzt, @ gsbm insert @sp_khyexytj_ncye select * from #KHYRERERERETURN DROP TABLE #Khyereturn
- Declare @sqlstring nvarchar (500) DECLARE @cond nvarchar (100) - here is also the place for query conditions EXECUTE SP_MAKECOND 'DWMC', @ khmc, @ cxzt, @gsbm, @ Cond Output
Set @sqlstring = 'SELECT DWMC, SUM (XHSL * HSJ) AS XSJE from Xsdda Where XSRQ <= @ venddate and xsrq> = @ VStartDate and' @sqlstring 'Group by dwmc'
Create table #sp_KHYEXYTJ_XSJE (dwmc nvarchar (250), xsje decimal (15,6)) Declare @ParmDefinition nvarchar (100) set @ParmDefinition = '@vKHMC nvarchar (250), @ vEndDate datetime, @ vStartDate datetime' insert into #sp_KHYEXYTJ_XSJE EXECUTE sp_executesql @SqlString, @ParmDefinition, @ vKHMC = @ KHMC, @ vEndDate = @ EndDate, @ vStartDate = @ StartDate-- production output table create table #sp_KHYEXYTJ_ReslutTable (dwmc nvarchar (250), ncye decimal (15,6), ssye Decimal (15, 6), BQXS Decimal (15, 6), QMYE Decimal (15, 6), KHXYE DECIMAL (15, 6)) - The following is a pile of chaos. Originally, put the data into the cursor, then looping the cursor - but the speed is very slow, a total of 3 data sheets, the three chapter data is 1K, 4K, 2W, - use the cursor processing, Finally returned to the statistics table 600 data for 27 seconds, a comparison time - maybe I wrote, but this time is really too long - so, it will be replaced by a temporary table Processing, separate statistics of data required to be statistically, - then put in different temporary tables, and finally all data is associated according to DWMC fields, and output together. - The result of the final query, the same amount of data, used for 2 seconds. - For this time, I am still satisfied. Because the data is more data, and the previous cursor is 27s - it is not an equal order. No matter whether it is good, you can first take it out.
- Write output table and generate statistics list INSERT INTO #SP_KHYEXYTJ_RESLUTTABLE (DWMC, BQXS) Select DWMC, XSje from #SP_KHYEXYTJ_XSJE
- writing the output table update #sp_KHYEXYTJ_ReslutTable set # sp_KHYEXYTJ_ReslutTable.ssye = # sp_KHYEXYTJ_KHYE.khye from #sp_KHYEXYTJ_KHYE where # sp_KHYEXYTJ_ReslutTable.dwmc = # sp_KHYEXYTJ_KHYE.dwmc
- writing the output table update #sp_KHYEXYTJ_ReslutTable set # sp_KHYEXYTJ_ReslutTable.qmye = # sp_KHYEXYTJ_QMKHYE.QMkhye from #sp_KHYEXYTJ_QMKHYE where # sp_KHYEXYTJ_ReslutTable.dwmc = # sp_KHYEXYTJ_QMKHYE.dwmc
- writing the output table update #sp_KHYEXYTJ_ReslutTable set # sp_KHYEXYTJ_ReslutTable.ncye = # sp_KHYEXYTJ_NCYE.ncye from #sp_KHYEXYTJ_NCYE where # sp_KHYEXYTJ_ReslutTable.dwmc = # sp_KHYEXYTJ_NCYE.dwmc-- calculated update #sp_KHYEXYTJ_ReslutTable set # sp_KHYEXYTJ_ReslutTable.khxye = isnull (xhkhda.khxye, 0), # sp_khyexytj_resluttable.cxye = ABS (# sp_khyexytj_resluttable.ssye) -isnull (xhkhda.khxye, 0) from xhkhda where # sp_khyexytj_resluttable.dwmc = xhkhda.dwmc
- The final output is the result, because it is calculated, in order to reduce the error, in the calculation - all use 6 decimals, when output, take two decimals to output
- Enter table SELECT RTRIM (LTRIM (DWMC)) AS DWMC, Cast (ISNULL (NCYE, 0), 2) AS Decimal (15, 2)) AS NCYE, CAST (Round (isnull (SSYE, 0), 2) As Decimal (15, 2)) AS SSYE, CAST (Round (Isnull (BQXS, 0), 2) As Decimal (15, 2)) AS BQXS, CAST (Round (ISNULL (QMYE, 0), 2) As Decimal (15, 2)) AS QMYE, Cast (Isn (Isnull (Khxye, 0), 2) As Decimal (15, 2)) AS KHXYE, CAST (Round (isnull (Case When Cxyee <= 0 Then 0 When) Cxye> 0 Then Cxye End, 0), 2) As Decimal (15, 2)) AS CXYE
From #SP_KHYEXYTJ_RESLUTTABLEGO The three stored procedures are more chaotic, after all, is my first stored procedure. Can understand. However, you can't write such a mess, otherwise you will not forgive. In these three stored procedures, there are still some parts of whether they are still unsatisfactory, in all, store procedure A calls stored procedure B, and stored procedure B needs to return a result set A. Although used a temporary table as a storage, it is always imagined to write OO, returning the temporary table or the result set object. I remember that when I wrote this place, I accidentally saw the information, and I can put the result set to Output in the cursor. However, I forgot to see where it was seen, but also made lazy, so I just used a temporary table directly. Anyway, the cursor returned, but also put it in the temporary table.
