table类型和游标的使用

[sql]代码库

/*
create type BOMVersion as table(
	pcode varchar(18),
	[month] int,
	code varchar(18),
	unit varchar(3),
	quantity decimal(18,3)
)
GO
*/

CREATE PROCEDURE BOMCompare
(
  @year1 as int,
  @month1 as int,
  @year2 as int,
  @month2 as int,
  @matnr as varchar(18)
)
AS
BEGIN
  DECLARE @bom1 BOMVersion
  DECLARE @bom2 BOMVersion
  DECLARE @pcode varchar(18)
  DECLARE @month int,
  DECLARE @code varchar(18),
  DECLARE @unit varchar(3),
  DECLARE @quan decimal(18,3)

  insert into #bomcompareresult
  SELECT
    bom1.*,
    NULL as month2,
    NULL as code2,
    NULL as unit2,
    NULL as quantity2
  FROM @bom2 as bom1
  
  DECLARE i CURSOR FOR SELECT * FROM @bom1 as bom1
  OPEN i
  
  FETCH NEXT FROM i INTO @pcode, @month, @code, @unit, @quan
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    update #bomcompareresult SET month2=@month, code2=@code, unit2=@unit, quantity2=@quan
    WHERE pcode = @pcode and code = @code
    
    if(@@ROWCOUNT = 0)
    BEGIN
      INSERT INTO #bomcompareresult values (@pcode, null, null, null, null, @month, @code, @unit, @quan)
    END
    
	FETCH NEXT FROM i INTO @pcode, @month, @code, @unit, @quan
  END
  
  SELECT * FROM #bomcompareresult ORDER pcode
END