最近写的一个使用了表变量参数的存储过程

alter proc SP_DISPLAY_PART_RATIO_BY_MODEL_NO
(
  @drive varchar(20),
  @epullid varchar(30),
  @functionname varchar(50),
  @invLoc varchar(20),
  @server varchar(20),
  @sqlmodel varchar(max)
)
as
  declare @ModelList MCS_ModelList 
  declare  @sch_id varchar(12) 
  declare  @revision numeric(18, 0) 
  declare  @trans_id varchar(20) 
  declare  @model varchar(20) 
  declare  @start_date datetime
  declare  @end_date datetime
  declare  @line varchar(30) 
  declare  @qty numeric(18, 0) 
  declare  @shift_qty bigint 
  declare  @family varchar(20)
  
  CREATE TABLE #t(
    [sch_id] [varchar](12) NOT NULL,
    [revision] [numeric](18, 0) NOT NULL,
    [trans_id] [varchar](12) NOT NULL,
    [model] [varchar](20) NOT NULL,
    [start_date] [datetime] NOT NULL,
    [end_date] [datetime] NOT NULL,
    [line] [varchar](30) NOT NULL,
    [qty] [numeric](18, 0) NOT NULL,
    [shift_qty] [bigint] NOT NULL,
    [family] [varchar](20) NULL
  )
  insert into #t
  exec(@sqlmodel)
  
  declare cur_model_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for    
  select 
  [model]
   from #t 
   open cur_model_sql 
   fetch next from cur_model_sql into @model
     while @@fetch_status=0    
     begin  
       select top 1 @trans_id = trans_id from MC_EPULL_MODEL_HOURLY(nolock) where EPULL_ID = @epullid and MODEL_NO = @model
       if(LEN(@trans_id)>0)
       begin
       insert into @ModelList([model],[prime],[item_id],
       [item_nr],[item_desc],[family],[class],[component_quantity],[component_yield_factor],[component_sequence_id]) 
       exec('select '''+@model+''' as model,a.* from '+@functionname+'('''+@model+''') a')
     end
     fetch next from cur_model_sql into @model
     end
     deallocate cur_model_sql 
  
  create table #tempMainPart(
      [model] [varchar](20) NOT NULL,
      [trans_id] [varchar](10) NOT NULL,
      [new_item] [int] NOT NULL,
      [prime] [varchar](20) NULL,
      [item_nr] [varchar](20) NULL,
      [family] [varchar](20) NULL,
      [item_desc] [varchar](80) NULL,
      [component_yield_factor] [tinyint] NULL,
      [component_sequence_id] [int] NULL,
      [item_class] [varchar](10) NULL,
      [component_quantity] [tinyint] NULL,
      [requested_per] [int] NOT NULL,
      [required_qty] [int] NULL,
      [plan_qty] [int] NOT NULL,
      [part_available] [int] NULL,
      [item_type] [varchar](1) NOT NULL
  )
  
  create table #tempMainPartGroup(
      [model] [varchar](20) NOT NULL,
      [trans_id] [varchar](10) NOT NULL,
      [new_item] [int] NOT NULL,
      [prime] [varchar](20) NULL,
      [item_nr] [varchar](20) NULL,
      [family] [varchar](20) NULL,
      [item_desc] [varchar](80) NULL,
      [item_class] [varchar](10) NULL,
      [item_type] [varchar](1) NOT NULL,
      [count] int
  )
  
  create table #tempPrimaryAlternatePart(
       orig_part_no varchar(20),
       alt_part_no varchar(20),
       Part_Ratio numeric(18,0)
  )
  
    declare cur_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for    
  select [sch_id],[revision],
  [model],[start_date],
  [end_date],[line],[qty],[shift_qty],[family]
   from #t 
   open cur_sql 
   fetch next from cur_sql into @sch_id ,@revision ,
                     @model ,
                     @start_date,@end_date ,
                     @line ,@qty ,@shift_qty ,
                     @family 
     while @@fetch_status=0    
     begin  
       select top 1 @trans_id = trans_id from MC_EPULL_MODEL_HOURLY(nolock) where EPULL_ID = @epullid and MODEL_NO = @model
       if(LEN(@trans_id)>0)
       begin
       insert into #tempMainPart 
       exec SP_DISPLAY_MAIN_PART_BY_MODEL_NO @drive,@model,@epullid,@trans_id,@tab = @ModelList
     end
     fetch next from cur_sql into @sch_id ,@revision ,
                   @model ,@start_date,
                   @end_date ,@line ,
                   @qty ,@shift_qty ,
                   @family 
     end
     deallocate cur_sql 
     
     insert into #tempMainPartGroup
     select 
       [model]
      ,[trans_id]
      ,[new_item]
      ,[prime]
      ,[item_nr]
      ,[family]
      ,[item_desc]
      ,[item_class]
      ,[item_type]
      ,COUNT(*) as [count]
      from #tempMainPart 
      group by
       [model]
      ,[trans_id]
      ,[new_item]
      ,[prime]
      ,[item_nr]
      ,[family]
      ,[item_desc]
      ,[item_class]
      ,[item_type]

    declare  @new_item int
    declare  @prime    varchar(20) 
    declare  @item_nr  varchar(20) 
    declare  @item_desc varchar(80)
    declare  @item_class varchar(10) 
    declare  @item_type varchar(1) 

      declare cur_ratio_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for 
      select 
       [model]               
      ,[trans_id]
      ,[new_item]
      ,[prime]
      ,[item_nr]
      ,[family]
      ,[item_desc]
      ,[item_class]
      ,[item_type]
      from #tempMainPartGroup
      open cur_ratio_sql 
      fetch next from cur_ratio_sql into @model,
                                         @trans_id,
                                         @new_item, 
                                         @prime,    
                                         @item_nr,  
                                         @family, 
                                         @item_desc,
                                         @item_class,
                                         @item_type
   while @@fetch_status=0    
     begin
           insert into #tempPrimaryAlternatePart
           exec SP_GET_PRIMARY_ALTERNATE_Part @item_nr,@model,@epullid,@trans_id,@tab = @ModelList
           fetch next from cur_ratio_sql into @model,
                                              @trans_id,
                                              @new_item, 
                                              @prime,    
                                              @item_nr,  
                                              @family, 
                                              @item_desc,
                                              @item_class,
                                              @item_type 
     end                  
    deallocate cur_ratio_sql 
    
  select distinct part.item_nr as PrimaryNo, 
         isnull(partratio.alt_part_no,part.item_nr) as PartNo,
         part.item_desc,
         part.family,
         ISNULL(partratio.Part_Ratio,0) as part_ratio
    into #tempPrimaryAlternatePartRatio
    from #tempMainPartGroup part
    left join #tempPrimaryAlternatePart partratio
    on part.item_nr = partratio.orig_part_no

    declare @fiscalYear varchar(10)
    declare @quarter varchar(4)
    declare @startTime datetime
    declare @endTime datetime
    set @fiscalYear = (select FISCAL_YEAR  from MCS_FISCAL_YEAR(nolock) 
        where START_DATE<=convert(varchar(10),GETDATE(),10) 
          and END_DATE>=convert(varchar(10),GETDATE(),10))
    
    set @quarter = (select QUARTER  from MCS_FISCAL_YEAR(nolock) 
        where START_DATE<=convert(varchar(10),GETDATE(),10) 
          and END_DATE>=convert(varchar(10),GETDATE(),10))
    
    set @startTime = (SELECT [START_DATE] FROM dbo.MCS_FISCAL_YEAR(nolock) 
        where START_DATE<=convert(varchar(10),GETDATE(),10) and END_DATE>=convert(varchar(10),GETDATE(),10))
        
    set @endTime = (SELECT [End_DATE] FROM dbo.MCS_FISCAL_YEAR(nolock) 
        where START_DATE<=convert(varchar(10),GETDATE(),10) and END_DATE>=convert(varchar(10),GETDATE(),10))
  
  create table #tempPartAchivedQuantity(
      part_item_no VARCHAR(20),  
        achivedQuantity int
  )
  
  create table #tempPartAchivedRatio(
      part_item_no VARCHAR(20),  
        partratio int
  )
  
  declare @PartNo varchar(20)
  declare cur_part_sql cursor for
    select PartNo
         from #tempPrimaryAlternatePartRatio
         open cur_part_sql
         fetch next from cur_part_sql into @PartNo
     while @@fetch_status=0    
     begin
        insert into #tempPartAchivedQuantity(part_item_no,achivedQuantity)
        select * from DISPLAY_PART_ACHIVED_QUANTITY(@PartNo,@startTime,@endTime)
        fetch next from cur_part_sql into @PartNo
     end
     deallocate cur_part_sql 
     
   select 0 as RowNo,
         (case when PrimaryNo = PartNo then 'O' else 'E' end) as RowInd, 
          PartRatio.*,
       ISNULL(AchivedQuantity.achivedQuantity, 0) as achivedQuantity,
       ISNULL(AchivedRatio.partratio, 0) as partratio
       into #tempPartAll
    from #tempPrimaryAlternatePartRatio PartRatio
    left join #tempPartAchivedQuantity AchivedQuantity
    on PartRatio.PartNo = AchivedQuantity.part_item_no
    left join #tempPartAchivedRatio  AchivedRatio
      on PartRatio.PartNo = AchivedRatio.part_item_no
    order by PrimaryNo asc,RowInd desc
  
    declare @RowNo int
    declare @RowInd varchar(1)
    declare @PrimaryNo varchar(20)
    declare @part_ratio int
    declare @achivedQuantity int
    declare @partratio int
    
    CREATE TABLE #tempVendor(
    PrimaryNo varchar(20),
    [item_id] [int] ,
    [item_nr] [varchar](20) ,
    [vendor_code] [varchar](20) ,
    [inv_loc] [varchar](20) ,
    [qty] [bigint] ,
    [update_datetime] [datetime] ,
    [item_desc] [varchar](50) ,
    [va_std_pack_l2] [bigint] 
      )
    
    declare cur_partall_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for
    select *
         from #tempPartAll
         open cur_partall_sql
       fetch next from cur_partall_sql into @RowNo,@RowInd,@PrimaryNo,
                                          @PartNo,@item_desc,@family,@part_ratio,
                                          @achivedQuantity,@partratio
    while @@fetch_status=0    
    begin
     insert into #tempVendor
     exec SP_DISPLAY_PARTVENDOR @PrimaryNo,@drive,@PartNo,@InvLoc,@server
     fetch next from cur_partall_sql into @RowNo,@RowInd,@PrimaryNo,
                                          @PartNo,@item_desc,@family,@part_ratio,
                                          @achivedQuantity,@partratio
    end
    deallocate cur_partall_sql 
    
    select distinct a.RowNo,
       a.RowInd,
       a.PrimaryNo,
       a.PartNo,
       a.item_desc,
       a.family,
       a.part_ratio,
       a.achivedQuantity,
       a.partratio,
       ISNULL(a.partratio, 0) - ISNULL(a.part_ratio, 0) as partdelta,
       b.inv_loc as boh,
       b.vendor_code,
       b.qty as [@7am],
       b.update_datetime,
       b.va_std_pack_l2
       into #tempPartVendor
    from #tempPartAll a
    left join #tempVendor b
    on a.PrimaryNo = b.PrimaryNo
     and a.PartNo = b.item_nr;
     
    WITH CTE AS (SELECT RowNo,  
    ROW_NUMBER() OVER(  
    PARTITION BY PartNo,RowInd
    order by PrimaryNo,RowInd ,boh ) - 1 AS x_new  
    FROM #tempPartVendor)  
    UPDATE CTE  
    SET RowNo = x_new 
     
    select * into #tempMainPartVendor from #tempPartVendor k where k.PrimaryNo = k.PartNo
    select a.*,
       isnull(dbo.Func_Get_TargetRaio(a.PartNo, a.Vendor_Code, @quarter, @fiscalYear),0) as Ratio,
       isnull(dbo.Func_Get_BuyQty(a.PartNo, a.Vendor_Code,@startTime,@endTime),0) as buyerqty,
       ISNULL(dbo.Func_Get_AchievedPercent(a.PartNo, a.Vendor_Code,@startTime,@endTime),0) as AchievedPercent
    from #tempPartVendor a 
    where a.PrimaryNo in (select PrimaryNo from #tempMainPartVendor)
       order by a.PrimaryNo asc,RowInd desc,boh asc

--=======================================下面是call 这个存储过程================================================================

declare @sql varchar(max)
set @sql =
'SELECT TBL2.*, TBL3.Family as Family' +
'  FROM (SELECT SCH_ID, REVISION' +
'          FROM MCS_PC_SCHEDULE_MASTER WITH(NOLOCK)' +
'         WHERE CATEGORY = ''DRIVE''' +
'           AND PLAN_BUILDING = ''B1''' +
'           AND PLAN_LEVEL = ''L2''' +
'           AND PUBLISH_FLAG = ''Y''' +
'           AND CONVERT(VARCHAR(10), ENTRY_DATE, 103) =' +
'               CONVERT(VARCHAR(10), ''01/12/2012 07:00:00'', 103)) TBL1' +
' INNER JOIN (SELECT SCH_ID,' +
'                    REVISION,' +
'                    TRANS_ID,' +
'                    MODEL,' +
'                    START_DATE,' +
'                    END_DATE,' +
'                    LINE,' +
'                    QTY,' +
'                    IsNULL((SELECT SUM(PLAN_QTY)' +
'                             FROM MC_EPULL_MODEL_HOURLY WITH(NOLOCK)' +
'                            WHERE EPULL_ID = ''L2NPLHDA-121201-000''' +
'                              AND MODEL_NO = A.MODEL' +
'                              and TRANS_ID = A.TRANS_ID' +
'                              AND REVISION = 0' +
'                              AND CONVERT(DATETIME, PLAN_DATE, 103) <=' +
'                                  CONVERT(DATETIME,' +
'                                          ''01/12/2012 18:59:59'',' +
'                                          103)' +
'                              AND CONVERT(DATETIME, PLAN_DATE, 103) >=' +
'                                  CONVERT(DATETIME,' +
'                                          ''01/12/2012 07:00:00'',' +
'                                          103)),' +
'                           0) SHIFT_QTY' +
'               FROM MCS_PC_SCHEDULE_TRANSMAIN A WITH(NOLOCK)' +
'              WHERE PUBLISH_FLAG = ''Y''' +
'                AND CONVERT(DATETIME, START_DATE, 103) <=' +
'                    CONVERT(DATETIME, ''01/12/2012 18:59:59'', 103)' +
'                AND CONVERT(DATETIME, END_DATE, 103) >=' +
'                    CONVERT(DATETIME, ''01/12/2012 07:00:00'', 103)) TBL2' +
'    on TBL1.SCH_ID = TBL2.SCH_ID' +
'   AND TBL1.REVISION = TBL2.REVISION' +
'  Left outer join MCS_ITEM TBL3 WITH(NOLOCK)' +
'    on TBL3.Item_Nr = TBL2.MODEL' +
' ORDER BY LINE, END_DATE, TBL2.MODEL'
exec SP_DISPLAY_PART_RATIO_BY_MODEL_NO 'DRIVE','L2NPLHDA-121201-000',
'DISPLAY_DRV_CLEANROOM_BOM_BY_MODEL','INV_LOC_STKMAIN','ORACLE_SQL',@sql

在使用这个存储过程之前,没有使用表变量,每次在游标内部都要select一个表值函数,而有两个游标都需要用到这个表值函数,那么重复的查询导致了严重的性能问题.

而现在用sql server 2008里的新技术,表变量参数解决了这个问题,先把表值函数的结果放进一个表变量里,然后在游标循环的时候就可以直间使用表变量了,而不用再

查询一次表值函数. 在没有使用表变量为参数时,运行时间为1分34秒,在使用后降为10秒.性能的提升是非常明显的!

注意,在使用表变量参数前,要先定义一个用户自定义数据类型的表结构

CREATE TYPE [dbo].[MCS_ModelList] AS TABLE(
  [model] [varchar](20) NULL,
  [prime] [varchar](20) NULL,
  [item_id] [varchar](20) NULL,
  [item_nr] [varchar](20) NULL,
  [item_desc] [varchar](80) NULL,
  [family] [varchar](20) NULL,
  [class] [varchar](10) NULL,
  [component_quantity] [tinyint] NULL,
  [component_yield_factor] [tinyint] NULL,
  [component_sequence_id] [int] NULL
)

在存储过程里面定义这个数据类型的变量(这里MCS_ModelList 就是定义的类型名称)

declare @ModelList MCS_ModelList 

然后就可以像普通表一样操作它 select * from@ModelList update @ModelList ......