笔记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
