笔记127 广西空军机关油站存储过程更新(修改)

 1 --广西空军机关油站存储过程更新(修改)
 2 IF EXISTS ( SELECT  *
 3             FROM    sysobjects
 4             WHERE   id = OBJECT_ID(N'[dbo].[CT_FuelingData_ICFulSumRep]')
 5                     AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
 6     DROP PROCEDURE [dbo].[CT_FuelingData_ICFulSumRep]
 7 
 8 USE [GPOSDB]
 9 GO
10 /****** 对象:  StoredProcedure [dbo].[CT_FuelingData_ICFulSumRep]    脚本日期: 04/27/2012 10:01:48 ******/
11 SET ANSI_NULLS ON
12 GO
13 SET QUOTED_IDENTIFIER ON
14 GO
15 
16 -----------------------------------------------------------------
17 -- Name: [dbo].[CT_OuterCard],,,,,[dbo].[CT_FuelingData]
18 -- Author: 李长均
19 -- DateTime: 2012-4-24
20 -- Description: Data Table[CT_OuterCard],[CT_FuelingData]
21 -- update:增加了一些判断语句
22 -- updatedate：2012-5-3
23 -----------------------------------------------------------------
24 
25 
26 CREATE  PROC [dbo].[CT_FuelingData_ICFulSumRep]
27     @VC_OC_Company VARCHAR(50) ,
28     @VC_OC_CarNO VARCHAR(50) ,   --@VC_OC_CardNO 卡号
29     @Starttime VARCHAR(50) ,
30     @Endtime VARCHAR(50)
31 AS
32     DECLARE @TSQL VARCHAR(4000)--T——sql语句
33     DECLARE @SQL VARCHAR(4000)--SELECT 语句
34     DECLARE @ConSQL VARCHAR(4000)--group by 语句
35     DECLARE @CdtSQL VARCHAR(4000)--条件语句
36     BEGIN
37         SET @SQL = ' SELECT CT_FuelingData.VC_FD_Cardno,CT_OuterCard.VC_OC_CarNO,CT_OuterCard.VC_OC_Company,CT_FuelingData.VC_FD_OilType,'
38             + ' sum(CT_FuelingData.DE_FD_Volume) AS Volume ,sum(CT_FuelingData.DE_FD_Amount) AS Amount FROM CT_FuelingData INNER JOIN '
39             + ' CT_OuterCard  ON CT_FuelingData.VC_FD_Cardno=CT_OuterCard.VC_OC_CardNO '
40             + ' WHERE 1=1'
41            
42         SET @CdtSQL = ''
43         IF ( @VC_OC_Company <> '' )
44             BEGIN
45                 SET @CdtSQL = @CdtSQL
46                     + 'and CT_OuterCard.VC_OC_Company like ''%'
47                     + @VC_OC_Company + '%'''
48             END    
49         IF ( @VC_OC_CarNO <> '' )
50             BEGIN
51                 SET @CdtSQL = @CdtSQL
52                     + ' and CT_OuterCard.VC_OC_CardNO like ''%' + @VC_OC_CarNO
53                     + '%'''
54             END    
55         IF ( @Starttime <> '' )
56             BEGIN
57                 SET @CdtSQL = @CdtSQL + ' and CT_FuelingData.D_FD_DateTime>'''
58                     + @Starttime + ''''
59             END    
60  
61         IF ( @Endtime <> '' )
62             BEGIN
63                 SET @CdtSQL = @CdtSQL + ' and CT_FuelingData.D_FD_DateTime<'''
64                     + @Endtime + ''''
65             END
66         SET @ConSQL = ' GROUP BY CT_FuelingData.VC_FD_OilType,CT_FuelingData.VC_FD_Cardno,CT_OuterCard.VC_OC_CarNO,CT_OuterCard.VC_OC_Company '
67         SET @TSQL = @SQL + @CdtSQL + @ConSQL
68         EXEC(@TSQL)
69     END