请问那位高手可以把这个存储过程改成不使用游标的，万分感谢，200分相送！！！

[pinghero]

偶是新手，为方便大家帮忙分析，我把它贴全了。分数稍后我再狂加！！！

-----------------------------------------------------------------------------------------

CREATE procedure sp_dis_process_imei
@process_day varchar(50)
as
begin
--get the data type
declare @type_sellthru varchar(20)
declare @type_return varchar(20)
declare @type_transfer varchar(20)
select @type_sellthru=data_code from tb_dis_data_type where data_name = 'sellthru-dis'
select @type_return=data_code from tb_dis_data_type where data_name = 'return-dis'
select @type_transfer=data_code from tb_dis_data_type where data_name = 'transfer-dis'

--declare variable
declare @status_flag int
declare @exist_flag int
declare @record_log_id int
declare @record_dis_code varchar(50)
declare @record_imei varchar(50)
declare @record_city_code varchar(50)
declare @record_sellthru_date varchar(50)
declare @record_data_type varchar(50)
declare @latest_data_type int
declare @latest_dis_code varchar(50)
declare @latest_sell_date varchar(50)
declare @shipment_shipdate varchar(50)
declare @shipment_discode varchar(50)
declare @city_code varchar(50)

--get the imei collection from shipment and sellthru' cross
declare @imei_shipment_sellthru table(
imei varchar(50),
dis_code varchar(50),
ship_date varchar(50)
)
insert into @imei_shipment_sellthru
select shipment.imei,mapping.dis_code, convert(varchar(10),shipment.ship_date,120)
from tb_dis_sellthru sellthru
inner join sps_imei_shipment shipment on shipment.imei = sellthru.imei
inner join sps_customer_mapping mapping on mapping.cus_no = shipment.cust_no
where update_by = 'From Distributor Uploading'
and convert(varchar(10), sellthru.update_date, 120) = @process_day

declare cur_today_imei cursor for
select log_id, dis_code,imei,city_code,data_type_id,
sellthru_date
from tb_dis_sellthru
where update_by = 'From Distributor Uploading'
and convert(varchar(10),update_date,120) = @process_day
open cur_today_imei
fetch cur_today_imei into @record_log_id, @record_dis_code, @record_imei,
@record_city_code, @record_data_type, @record_sellthru_date
while (@@FETCH_STATUS = 0)
begin
set @status_flag = 0

--check factory shipment
select @exist_flag=count(*) from @imei_shipment_sellthru where imei = @record_imei
if (@exist_flag < 1) set @status_flag = -1

--check the sellthru IMEI
if (@status_flag = 0 and @record_data_type = @type_sellthru)
begin
select @exist_flag=count(*) from tb_dis_sellthru
where imei = @record_imei and log_id < @record_log_id
and update_by = 'From Distributor Uploading'

--the imei existed in the historical record
if (@exist_flag >= 1)
begin
select top 1 @latest_data_type=data_type_id, @latest_dis_code=dis_code,
@latest_sell_date=sellthru_date
from tb_dis_sellthru
where imei = @record_imei and log_id < @record_log_id
and update_by = 'From Distributor Uploading'
order by log_id desc

if (@latest_data_type <> @type_sellthru and @latest_dis_code <> @record_dis_code )
set @status_flag = 1
if (@latest_data_type = @type_sellthru and @latest_dis_code = @record_dis_code )
set @status_flag = -2
if (@latest_data_type = @type_sellthru and @latest_dis_code <> @record_dis_code )
set @status_flag = 2
end

[pinghero]

继续----------------------------------------------
--the imei existed in the historical record

--the imei doesn't exist in the historical record
else begin
select @shipment_shipdate=ship_date, @shipment_discode=dis_code from @imei_shipment_sellthru
where imei = @record_imei

if (@shipment_discode <> @record_dis_code)
set @status_flag = 1
if (convert(datetime, @shipment_shipdate) > convert(datetime, @record_sellthru_date) )
set @status_flag = 3
end
--the imei doesn't exist in the historical record
end

--check the return imei
if (@status_flag = 0 and @record_data_type = @type_return)
begin
select @exist_flag=count(*) from tb_dis_sellthru
where imei = @record_imei and log_id < @record_log_id
and update_by = 'From Distributor Uploading'

--the imei existed in the historical record
if (@exist_flag >= 1)
begin
select top 1 @latest_data_type=data_type_id, @latest_dis_code=dis_code,
@latest_sell_date=sellthru_date,@city_code=city_code from tb_dis_sellthru
where imei = @record_imei and log_id < @record_log_id
and update_by = 'From Distributor Uploading'
order by log_id desc
if (@latest_data_type = @type_sellthru and @latest_dis_code = @record_dis_code)
update tb_dis_sellthru set city_code= city_code where log_id = @record_log_id

if (@latest_data_type <> @type_sellthru)
set @status_flag = 4
if (@latest_data_type = @type_return and @latest_dis_code = @record_dis_code )
set @status_flag = -3
if (@latest_data_type = @type_return and @latest_dis_code <> @record_dis_code )
set @status_flag = 6
if (@latest_data_type = @type_sellthru and @latest_dis_code <> @record_dis_code)
set @status_flag = 5
end
--the imei existed in the historical record

--the imei doesn't exist in the historical record
else begin
set @status_flag = 4
end
--the imei doesn't exist in the historical record
end

--check the transfer IMEI
if (@status_flag = 0 and @record_data_type = @type_transfer)
begin
select @exist_flag=count(*) from tb_dis_sellthru
where imei = @record_imei and log_id < @record_log_id
and update_by = 'From Distributor Uploading'

--the imei existed in the historical record
if (@exist_flag >= 1)
begin
select top 1 @latest_data_type=data_type_id, @latest_dis_code=dis_code,
@latest_sell_date=sellthru_date
from tb_dis_sellthru
where imei = @record_imei and log_id < @record_log_id
and update_by = 'From Distributor Uploading'
order by log_id desc

if (@latest_data_type = @type_transfer and @latest_dis_code <> @record_dis_code )
set @status_flag = 9
if (@latest_data_type = @type_transfer and @latest_dis_code = @record_dis_code )
set @status_flag = -4
if (@latest_data_type = @type_sellthru and @latest_dis_code <> @record_dis_code )
set @status_flag = 7
end
--the imei existed in the historical record

--the imei doesn't exist in the historical record
else begin
select @shipment_shipdate=ship_date, @shipment_discode=dis_code from @imei_shipment_sellthru
where imei = @record_imei

if (@shipment_discode = @record_dis_code)
set @status_flag = 1
if ( convert(datetime, @shipment_shipdate) > convert(datetime,@record_sellthru_date) )
set @status_flag = 8
end
--the imei doesn't exist in the historical record
end

--modify the historical record flag
update tb_dis_sellthru
set f_last_valid = '0'
where imei = @record_imei
and update_by = 'From Distributor Uploading'

--set flag
update tb_dis_sellthru
set status_id = @status_flag, f_last_valid = '1'
where log_id = @record_log_id

fetch cur_today_imei into @record_log_id, @record_dis_code, @record_imei,
@record_city_code, @record_data_type, @record_sellthru_date
end
close cur_today_imei
deallocate cur_today_imei

update tb_dis_sellthru
set product = isnull(mapping.product_code,''), color_code = isnull(mapping.color_code,'')
from tb_dis_sellthru sellthru
inner join sps_imei_shipment shipment on shipment.imei = sellthru.imei
inner join sps_product_mapping mapping on mapping.item_no = shipment.item_no
where update_by = 'From Distributor Uploading'
and convert(varchar(10), sellthru.update_date, 120) = @process_day

update tb_dis_sellthru
set city_code = geo.city_code
from tb_dis_sellthru sellthru
inner join geography geo on geo.city_name = sellthru.city_code and geo.geo_level = 4
where update_by = 'From Distributor Uploading'
and convert(varchar(10), sellthru.update_date, 120) = @process_day
--aggregate data
end
GO

[humanNew]

你的太长了，没有注释。自己动用吧
结合选择语句使用

[kirao]

這麼多，我都沒力氣看下去了，只能幫你頂!