Process multirow data with cursor or without cursor[利用游标或者非游标处理多行数据]

Implement functionality using Cusor
/*
decription:
import data from BomIntraplantRoute_Buckets of ofg_response_staging into
ds_in_bom_intraplant_route_bucket of OFG_SLV_DEV

created by: Winstion He on 2008/1/2

create proc [dbo].[Pr_ToSolver_ds_in_bom_intraplant_route_bucket]
as
declare @BomIntraplantRouteId int
declare @BucketId int
declare @Yield float
declare @Tpt int
declare @Mfg float

--clear primitive data
if Exists(select * from OFG_SLV_DEV3.dbo.ds_in_bom_intraplant_route_bucket)
delete from OFG_SLV_DEV3.dbo.ds_in_bom_intraplant_route_bucket

declare c_BomIntraRoute_Buckets cursor for
select BomIntraplantRouteId,BucketId,Yield,Tpt,Mfg
from BomIntraplantRoute_Buckets
open c_BomIntraRoute_Buckets

fetch from c_BomIntraRoute_Buckets into @BomIntraplantRouteId,@BucketId,@Yield,@Tpt,@Mfg
while @@fetch_status=0 begin
--get bom_header_name,intraplant_route_name through @BomIntraplantRouteId
--from BomIntraplantRoute
declare @IntraplantRouteName varchar(128)
declare @BomId varchar(128)

select @BomId=A.BomId,@IntraplantRouteName=B.IntraplantRouteName
from BomIntraplantRoute as A
join IntraplantRoute as B
on A.IntraplantRouteId= B.IntraplantRouteId
where A.BomIntraplantRouteId=@BomIntraplantRouteId

--get bucketname based bucketid using proc
declare @bucketName varchar(6)
exec Pr_ToSolver_PopulateBucketName @BucketId,@bucketName output

--insert value to table
insert into OFG_SLV_DEV3.dbo.ds_in_bom_intraplant_route_bucket
(
bom_header_name,
bucket,
intraplant_route_name,
yield,
tpt,
mfg
)
values
(
@BomId,
@bucketName,
@IntraplantRouteName,
@Yield,
@Tpt,
@Mfg
)

if @@rowcount < 1 goto ErrorHandle
--next row
fetch from c_BomIntraRoute_Buckets into @BomIntraplantRouteId,@BucketId,@Yield,@Tpt,@Mfg

end

ErrorHandle:
print 'BUG!'

close c_BomIntraRoute_Buckets
deallocate c_BomIntraRoute_Buckets
return

implement functionality without using cursor

use ofg_response_staging

if object_id('Pr_ToSolver_ds_in_bom_intraplant_route_bucket') is not null
drop proc Pr_ToSolver_ds_in_bom_intraplant_route_bucket
go

create proc Pr_ToSolver_ds_in_bom_intraplant_route_bucket
as
if exists(select * from OFG_SLV_DEV3.dbo.ds_in_bom_intraplant_route_bucket)
delete from OFG_SLV_DEV3.dbo.ds_in_bom_intraplant_route_bucket

insert into OFG_SLV_DEV3.dbo.ds_in_bom_intraplant_route_bucket
(
    bom_header_name
    ,bucket
    ,intraplant_route_name
    ,yield
    ,tpt
    ,mfg
)
select
    bi.BomId
    ,ltrim(str(b.YearCode))+ case when b.WorkWeekCode <=9 then '0'+ ltrim(str(b.WorkWeekCode))
     when b.WorkWeekCode >9 then ltrim(str(b.workWeekCode))
     end as bucketname

    ,i.IntraplantRouteName
    ,birb.Yield
    , birb.Tpt
    ,birb.Mfg

from
      BomIntraplantRoute bi,
      BomIntraplantRoute_Buckets birb,
      IntraplantRoute i,
      Bucket b

where
    bi.BomIntraplantRouteId=birb.BomIntraplantRouteId
    and
    bi.IntraplantRouteId=i.IntraplantRouteId
    and
    birb.BucketId=b.BucketId

select
ltrim(str(b.YearCode))+ case when b.WorkWeekCode <=9 then '0'+ ltrim(str(b.WorkWeekCode))
when b.WorkWeekCode >9 then ltrim(str(b.workWeekCode))
end as bucketname

from Bucket b