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