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

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

--广西空军机关油站存储过程更新(修改)
IF EXISTS ( SELECT  *
            FROM    sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[CT_FuelingData_ICFulSumRep]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    DROP PROCEDURE [dbo].[CT_FuelingData_ICFulSumRep]

USE [GPOSDB]
GO
/****** 对象:  StoredProcedure [dbo].[CT_FuelingData_ICFulSumRep]    脚本日期: 04/27/2012 10:01:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-----------------------------------------------------------------
-- Name: [dbo].[CT_OuterCard],,,,,[dbo].[CT_FuelingData]
-- Author: 李长均
-- DateTime: 2012-4-24
-- Description: Data Table[CT_OuterCard],[CT_FuelingData]
-- update:增加了一些判断语句
-- updatedate：2012-5-3
-----------------------------------------------------------------


CREATE  PROC [dbo].[CT_FuelingData_ICFulSumRep]
    @VC_OC_Company VARCHAR(50) ,
    @VC_OC_CarNO VARCHAR(50) ,   --@VC_OC_CardNO 卡号
    @Starttime VARCHAR(50) ,
    @Endtime VARCHAR(50)
AS
    DECLARE @TSQL VARCHAR(4000)--T——sql语句
    DECLARE @SQL VARCHAR(4000)--SELECT 语句
    DECLARE @ConSQL VARCHAR(4000)--group by 语句
    DECLARE @CdtSQL VARCHAR(4000)--条件语句
    BEGIN
        SET @SQL = ' SELECT CT_FuelingData.VC_FD_Cardno,CT_OuterCard.VC_OC_CarNO,CT_OuterCard.VC_OC_Company,CT_FuelingData.VC_FD_OilType,'
            + ' sum(CT_FuelingData.DE_FD_Volume) AS Volume ,sum(CT_FuelingData.DE_FD_Amount) AS Amount FROM CT_FuelingData INNER JOIN '
            + ' CT_OuterCard  ON CT_FuelingData.VC_FD_Cardno=CT_OuterCard.VC_OC_CardNO '
            + ' WHERE 1=1'
           
        SET @CdtSQL = ''
        IF ( @VC_OC_Company <> '' )
            BEGIN
                SET @CdtSQL = @CdtSQL
                    + 'and CT_OuterCard.VC_OC_Company like ''%'
                    + @VC_OC_Company + '%'''
            END    
        IF ( @VC_OC_CarNO <> '' )
            BEGIN
                SET @CdtSQL = @CdtSQL
                    + ' and CT_OuterCard.VC_OC_CardNO like ''%' + @VC_OC_CarNO
                    + '%'''
            END    
        IF ( @Starttime <> '' )
            BEGIN
                SET @CdtSQL = @CdtSQL + ' and CT_FuelingData.D_FD_DateTime>'''
                    + @Starttime + ''''
            END    
 
        IF ( @Endtime <> '' )
            BEGIN
                SET @CdtSQL = @CdtSQL + ' and CT_FuelingData.D_FD_DateTime<'''
                    + @Endtime + ''''
            END
        SET @ConSQL = ' GROUP BY CT_FuelingData.VC_FD_OilType,CT_FuelingData.VC_FD_Cardno,CT_OuterCard.VC_OC_CarNO,CT_OuterCard.VC_OC_Company '
        SET @TSQL = @SQL + @CdtSQL + @ConSQL
        EXEC(@TSQL)
    END