T-SQL,表变量、动态T-SQL

USE [AndersenDataWarehouse]
GO
/****** 对象:  StoredProcedure [dbo].[Pro_DispGISInfo]    脚本日期: 03/27/2009 11:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    Bitl
-- Create date: 2009-3-27
-- Description:    提供GIS展示客单价、客流量、销售总额、店铺目标达成
-- =============================================

ALTER Procedure [dbo].[Pro_DispGISInfo]
    @Parament varchar(30),
    @Start_time varchar(30),
    @Finish_time varchar(30),
    @Region varchar(30),
    @Scope int
AS

--set @Region='B'
--set @Start_time='2009-01-02'
--set @Finish_time='2009-03-01'
begin
declare @SQL VARCHAR(200) -- 定义动态T-SQL字符串
declare    @Shop_Area table (Shop_Area char(10)) --处理存储@Region传过来的是'0'或者是'A',如果是'0'则表示全部门店,'A'表示A区域
--判断店铺区域是全部还是部份'0'表示全部、'A'表示A区
if @Region='0'
    insert into @Shop_Area(Shop_Area) select distinct 店铺编码 from dbo.dim_店铺 where 店铺区域 is not null
else
    insert into @Shop_Area(Shop_Area) select distinct 店铺编码 as Shop_Area from dbo.dim_店铺 where substring(店铺区域,1,1)=@Region

select
    Code,
    sum(Client_QTY) as Client_QTY,
    sum(Client_Price) as Client_Price,
    sum(Sales_Amount) as Sales_Amount,
    sum(Sales_Rate)as Sales_Rate
into #temp
from(
--客流量、客单价计算
    select
        店铺编号 as Code,
        count(单据号) as Client_QTY,
        sum(刷卡消费金额+现金消费金额+券消费金额)/count(单据号) as Client_Price,
        0 as Sales_Amount,
        0 as Sales_Rate
    from dbo.fact_销售主表_POS
    where 日期 between @Start_time and @Finish_time and 店铺编号 in (select Shop_Area from @Shop_Area)
    and 出入库类型='销售'
    group by 店铺编号
    union all
--营业总额计算 select * from dbo.fact_店铺营业情况_后台
    select
        店铺 as Code,
        0 as Client_QTY,
        0 as Client_Price,
        sum(总营业额) as Sales_Amount,
        0 as Sales_Rate
    from dbo.fact_店铺营业情况_后台
    where 日期 between @Start_time and @Finish_time and 店铺 in (select Shop_Area from @Shop_Area)
    group by 店铺
    union all
--目标达成
    select
        店铺编码 as Code,
        0 as Client_QTY,
        0 as Client_Price,
        0 as Sales_Amount,
    sum(营总业总额)/sum(目标营业额) as Sales_Rate
    from dbo.fact_门市部目标达成_店铺_后台
    where cast(substring(年月,1,4)+'-'+substring(年月,6,2)+'-10' as datetime) between @Start_time and @Finish_time and 店铺编码 in (select Shop_Area from @Shop_Area)
    group by 店铺编码
    )d group by Code

set @SQL='Select top '+cast(@Scope as varchar(2))+' Code, Row_number() OVER (Order by '+@Parament+' DESC) as R_Rank,Client_QTY,Client_Price,Sales_Amount,Sales_Rate from #temp'
exec(@SQL)
end