执行存储过程测试

--执行存储过程创建
if (exists (select * from sys.objects where name = 'p_areaChannelDistributionReport'))
    drop proc p_areaChannelDistributionReport
go

create proc p_areaChannelDistributionReport
as
BEGIN
    select COUNT(*) AS num from sa_agreement;

End

--调用、执行存储过程
exec p_areaChannelDistributionReport;





--循环使用
DECLARE @areaId  as varchar(40)

DECLARE cur_area CURSOR FOR 
SELECT nid AS areaId from t_area_info with (nolock) WHERE parentid=1

OPEN cur_area


fetch next from cur_area into @areaId  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
while @@fetch_status=0           --判断是否成功获取数据
begin

Print @areaId


fetch next from cur_area into @areaId  --将游标向下移1行
end

close cur_area                   --关闭游标
deallocate cur_area





--定义变量和设置值
DECLARE @areaId  as varchar(40)

Declare @yearString as varchar(6)
Set @yearString ='2021'



--增删改操作


Begin
Delete [areaChannelDistributionReport]
where
Not Exists (Select B.toChannel From #temp_areaChanneltargetValue AS B
WHere  B.toChannel = [areaChannelDistributionReport].toChannel
AND B.channelId = [areaChannelDistributionReport].channelId
AND B.areaId = [areaChannelDistributionReport].areaId
AND B.channelType = [areaChannelDistributionReport].channelType
AND B.khId = [areaChannelDistributionReport].khId
AND B.yearString = [areaChannelDistributionReport].yearString
)

end




Begin
Update [areaChannelDistributionReport]
Set [khId] = B.[khId]
,[khName] = B.[khName]
,[channelId] = B.[channelId]
,[areaId] = B.[areaId]
,[areaName] = B.[areaName]
,[regionId] = B.[regionId]
,[regionName] = B.[regionName]
,[channelType] = B.[channelType]
,[channelTypeName] = B.[channelTypeName]
,[toChannel] = B.[toChannel]
,[targetT1] = B.[targetT1]
,[targetT2] = B.[targetT2]
,[targetT3] = B.[targetT3]
,[targetT4] =   B.[targetT4]
,[targetT5] =    B.[targetT5]
,[targetT6] = B.[targetT6]
,[targetN1] = B.[targetN1]
,[targetN2] = B.[targetN2]
,[targetN3] = B.[targetN3]
,[targetN4] = B.[targetN4]
,[targetN5] = B.[targetN5]
,[yearString] = B.[yearString]
,[targetT31] = B.[targetT31]
,[targetT32] = B.[targetT32]
,[targetT33] = B.[targetT33]
,[targetT41] = B.[targetT41]
,[targetT42] = B.[targetT42]
,[targetT43] = B.[targetT43]
,[targetT44] = B.[targetT44]
,[confirm] = B.[confirm]

From #temp_areaChanneltargetValue AS B
WHere  B.toChannel = [areaChannelDistributionReport].toChannel
AND B.channelId = [areaChannelDistributionReport].channelId
AND B.areaId = [areaChannelDistributionReport].areaId
AND B.channelType = [areaChannelDistributionReport].channelType
AND B.khId = [areaChannelDistributionReport].khId
AND B.yearString = [areaChannelDistributionReport].yearString
end



Begin

insert into [areaChannelDistributionReport] (
khId ,
khName,
channelId,
areaId,
areaName,
regionId,
regionName,
channelType,
channelTypeName,
channelName,
toChannel,
targetT1,
targetT2,
targetT3,
targetT4,
targetT5,
targetT6,
targetN1,
targetN2,
targetN3,
targetN4,
targetN5 ,
yearString ,
targetT31,
targetT32,
targetT33,
targetT41,
targetT42,
  targetT43,
targetT44,
confirm
  )
Select 
khId ,
khName,
channelId,
areaId,
areaName,
regionId,
regionName,
channelType,
channelTypeName,
channelName,
toChannel,
targetT1,
targetT2,
targetT3,
targetT4,
targetT5,
targetT6,
targetN1,
targetN2,
targetN3,
targetN4,
targetN5 ,
yearString,
targetT31,
targetT32,
targetT33,
targetT41,
targetT42,
  targetT43,
targetT44,
confirm
From #temp_areaChanneltargetValue AS B
Where not exists (Select toChannel From [areaChannelDistributionReport]
WHere  B.toChannel = [areaChannelDistributionReport].toChannel
AND B.channelId = [areaChannelDistributionReport].channelId
AND B.areaId = [areaChannelDistributionReport].areaId
AND B.channelType = [areaChannelDistributionReport].channelType
AND B.khId = [areaChannelDistributionReport].khId
AND B.yearString = [areaChannelDistributionReport].yearString
)


end