Originally posted: http://community.9cbs.net/expert/topic/3485/3485588.xml? Temp = .8813745
- Example Data Create Table Sale (Date DateTime, Code Varchar (10), AMT INT) INSERT SALE SELECT '2004-10-22', 'AA', 15000UNION All Select '2004-10-22', 'BB', 18000UNION All SELECT '2004-10-22', 'CC', 20000Union All Select '2004-10-23', 'AA', 21000UNION All Select '2004-10-23', 'BB', 18500UNION All Select '2004 -10-23 ',' CC ', 19600
Create Table Dept (Code VARCHAR (10), Name Varchar (10)) Insert Dept Select 'AA', 'Chinese Restaurant' Union All Select 'BB', 'Western Restaurant' Union All Select 'CC', 'Room Part All Select 'DD', 'KTV Department'
Create Table Cost (Date DateTime, Code Varchar (10), AMT INT) INSERT COST SELECT '2004-10-22', 'AA', 5000UNION All SELECT '2004-10-22', 'BB', 7000UNION All SELECT ' 2004-10-22 ',' CC ', 11000UNION All Select' 2004-10-23 ',' AA ', 12000UNION All Select' 2004-10-23 ',' BB ', 8500UNION All Select' 2004-10-23 ',' cc ', 9600
Create Table Means (Code Varchar (10), SEQ Int, [Desc] VARCHAR (10), Formula Nvarchar (4000) Insert Means SELECT 'AA', 1, 'Sales Income', '^ Sale @ AMT ^' Union All SELECT 'AA', 2, 'Sales Cost', '^ Cost @ AMT ^' Union All Select 'AA', 3, 'Upload Profits',' ([1] - [2]) * 0.3'Union All Select 'AA', 4, 'Net Profit', '[1] - [2] - [3]' Go
/ * - Problem processing requirements Description
Write a calculated stored procedure, complete the data in the profitable table of the profit according to the basic materials and calculation methods!
If the user entered the query condition is the department code AA (@code), the date is: 2004-10-22 (@date)
The processes are as follows, 1: Read the code from the profit calculation method (Means) read code = aa to temporary table Select * Into #Temp from means where code = @code order by SEQ
2: Take each item with a cursor, press SEQ from a small to big order, such as the first item insert Into Gain_Detail (Code, Date, DESC, AMT) Select @ code, @ Date, @ DESC, Sale.amt from Sale WHERE code = @code and date = @date Other project Similar formula description: 1. ^ Table name @P 字 name ^: For example: ^ Sale @ AMT ^ indicates the value of the AMT field from the Sale entry, the value is code = @ Code and Date = @ Date 2. [SEQ]: For example: [1] - [2] - [3], [] is the calculation result item in front of this department, [1] is indicated by the sales revenue of the department [2] Indicates sales costs, others like 3. Other standard calculation expressions - * /
- Problem processing: - Formula Calculated Storage Procedure Create Proc P_calc @ Formula Nvarchar (4000), - The Formula @code Varchar (10), - Department Code @Date DateTime, - Calculated Date @AMT INT OUT - calculated results asclare @ s1 nvarchar (4000), @ s2 nvarchar (4000), @ i int, @ J int
- Exterior calculation set @ i = Patindex ('% ^% @% ^%', @ formula) while @i> 0begin select @ J = charIndex ('@', @ formula, @ i), @ S1 = Substring @ Formula, @ i, @ J- @ i), @ S2 = 'from' Substring (@ formula, @ i 1, @ J- @ i-1) 'Where code = @ code and date = @ Date ', @ I = charindex (' ^ ', @ormula, @ J), @ S1 = @ S1 SUBSTRING (@ formula, @ J, @ i- @ J 1), @ S2 =' select @ AMT = ' Substring (@ formula, @ J 1, @ i- @ J-1) @ S2 EXEC SP_EXECUTESQL @ S2, N '@ code varchar (10), @DATETETIME, @ AMT INT OUT', @ code, @ Date, @ amt out select @ formula = replace (@ formula, @ S1, @ AMT), @ i = patindex ('% ^% @% ^%', @ formula) End
- Calculate internal formula SELECT @ i = Patindex ('% [[[[】%]%', @ formula) while @i> 0begin select @ j = charIndex ('], @ formula, @ i), @ S1 = Substring (@ formula, @ i, @ J- @ i 1), @ S2 = 'select @ AMT = AMT from #t where seq =' substring (@ formula, @ i 1, @ J- @ i-1 ) EXEC SP_EXECUTESQL @ S2, N '@ AMT INT OUT', @ Amt Out Select @ Formula = Replace (@ Formula, @ S1, @ AMT), @ i = Patindex ('% [[[]%]%', @ formula ) End - calculation final result set @ S2 = 'select @amt =' @ formulaexec sp_executesql @ S2, n '@ AMT INT OUT', @ AMT OUTGO
- Create Proc P_Qry @ code varchar (10), department code to calculate, @Date DateTime - Calculated Date ASSET NoCount Oncreate Table #t (Code Varchar (10), Date Varchar (10) , [DESC] VARCHAR (10), AMT INT, SEQ INT)
Declare @dt varchar (10), @ desc var, @ formula nvarchar (4000), @ seq int
Set @ DT = Convert (Char (10), @ Date, 120)
declare tb cursor local forselect [desc], formula, seq from meanswhere code = @ code order by seqopen tbfetch tb into @ desc, @ formula, @ seqwhile @@ fetch_status = 0begin - calculated exec p_calc @ formula, @ code, @ Date, @ AMT OUT INSERT #T Values (@ code, @ dt, @ DESC, @ Amt, @ seq) fetch tb @ DESC, @ formula, @ seqendclose TBDEAllocate TB
SELECT department code = code, date = Date, project content = [dec], amount = AMT from #TGO
- Call EXEC P_QRY 'AA', '2004-10-22'go
- Delete Test DROP TABLE SALE, DEPT, COST, Meansdrop Proc P_QRY, P_CALCGO
/*--Test Results
