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

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 ......