笔记128 广西空军机关油站存储过程更新

 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 -----------------------------------------------------------------
22 
23 
24 CREATE  PROC [dbo].[CT_FuelingData_ICFulSumRep]
25     @VC_OC_Company VARCHAR(50) ,
26     @VC_OC_CarNO VARCHAR(50) ,
27     @Starttime VARCHAR(50) ,
28     @Endtime VARCHAR(50)
29 AS
30     DECLARE @TSQL VARCHAR(4000)--T——sql语句
31     DECLARE @SQL VARCHAR(4000)--SELECT 语句
32     DECLARE @ConSQL VARCHAR(4000)--group by 语句
33     DECLARE @CdtSQL VARCHAR(4000)--条件语句
34     BEGIN
35         SET @SQL = ' SELECT CT_FuelingData.VC_FD_Cardno,CT_OuterCard.VC_OC_CarNO,CT_OuterCard.VC_OC_Company,CT_FuelingData.VC_FD_OilType,'
36             + ' sum(CT_FuelingData.DE_FD_Volume) AS Volume ,sum(CT_FuelingData.DE_FD_Amount) AS Amount FROM CT_FuelingData INNER JOIN '
37             + ' CT_OuterCard  ON CT_FuelingData.VC_FD_Cardno=CT_OuterCard.VC_OC_CardNO '
38             + ' WHERE 1=1'
39            
40         SET @CdtSQL = ' and CT_OuterCard.VC_OC_Company like ''%'
41             + @VC_OC_Company + '%'''
42             + ' and CT_OuterCard.VC_OC_CarNO like ''%' + @VC_OC_CarNO + '%'''
43             + ' and CT_FuelingData.D_FD_DateTime>''' + @Starttime + ''''
44             + ' and CT_FuelingData.D_FD_DateTime<''' + @Endtime + ''''
45         SET @ConSQL = ' GROUP BY CT_FuelingData.VC_FD_OilType,CT_FuelingData.VC_FD_Cardno,CT_OuterCard.VC_OC_CarNO,CT_OuterCard.VC_OC_Company '
46         SET @TSQL = @SQL + @CdtSQL + @ConSQL
47         EXEC(@TSQL)
48     END