加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

sqlserver存储过程笔记

发布时间：2020-12-12 13:04:12 所属栏目：MsSql教程 来源：网络整理
导读：不带参数的存储过程 -- =============================================--不参数的存储过程-- =============================================if(exists (select * from sys.objects where name = 'sp_BaseSyncWithoutParam'))drop procedure sp_BaseSyncWit

不带参数的存储过程

-- =============================================
--不参数的存储过程
-- =============================================
if(exists (select * from sys.objects where name = 'sp_BaseSyncWithoutParam'))
	drop procedure sp_BaseSyncWithoutParam
go
create procedure sp_BaseSyncWithoutParam
as
	select * from tbl_test;

go
exec sp_BaseSyncWithoutParam;



带参数的存储过程

SET ANSI_NULLS ON --允许比较运算符返回true or false
GO
SET QUOTED_IDENTIFIER ON --当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔
GO

 IF(EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_BaseSync'))
	DROP PROCEDURE sp_BaseSync
go
CREATE PROCEDURE sp_BaseSync 
	@id int,@name varchar(20) output
AS
insert into tbl_test(name) values(@name);
GO 


DECLARE @id INT,@name varchar(20);
SET @id = 7;
set @name ='leo'
EXEC sp_BaseSync @id,@name out;

go
select * from tbl_test


带通配符的存储过程

-- =============================================
--带参数的存储过程
-- =============================================

IF(EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_BaseSync'))
	DROP PROCEDURE sp_BaseSync
go
CREATE PROCEDURE sp_BaseSync 
	@id int,@name varchar(20) output
AS
	select * from tbl_test where name like @name;
GO 



DECLARE @id INT,@name varchar(20);
SET @id = 7;
set @name ='%'
EXEC sp_BaseSync @id,@name ;




-- =============================================
--带游标的存储过程
-- =============================================

if(exists (select * from sys.objects where name= 'sp_BaseSyncWithCursor'))
	drop proc sp_BaseSyncWithCursor;
go
create proc sp_BaseSyncWithCursor

as
declare @id int,@name varchar(20);
declare resultSet cursor for select * from tbl_test
open resultSet
fetch next from  resultSet into @id,@name;
 print @@fetch_status
while @@fetch_status=0
begin
update tbl_test set name='txm' where id=@id;
fetch next from resultSet into @id,@name;
end
close resultSet;
deallocate resultSet
go


exec sp_BaseSyncWithCursor


-- =============================================
--使用分页的存储过程
-- =============================================

if(exists (select * from sys.objects where name= 'pro_page'))
	drop proc pro_page;
create proc pro_page
    @startIndex int,@endIndex int
as
    select count(*) from tbl_test;    
    select * from (
        select row_number() over(order by id) as rowId,* from tbl_test 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1,6








DEMO功能：用于后台数据同步

?USE [CRMData_Test]
GO
/****** Object:  StoredProcedure [dbo].[sp_BaseSync]    Script Date: 07/17/2015 17:03:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if(exists (select * from sys.objects where name= 'sp_BaseSync'))
	drop proc sp_BaseSync
go
create proc [dbo].[sp_BaseSync]
	@param1  varchar(1000),--GROUP_ID
	@param2  varchar(1000),--NULL
	@param3  varchar(1000),--NULL
	@param4  varchar(1000),--NULL
	@param5  int			 --date diff
as

/**********************准备临时表代码块开始*************************************************/
begin try
--get server location
declare
	@linksrv varchar(100),@sys_last_upd datetime;

select @linksrv=Value1 from SysParam where TypeID='Database' AND Code='JwData' AND GroupID=@param1
/**********************准备Item临时表*********/
if object_id('tempdb.dbo.##tempItem') is not null Begin
    drop table ##tempItem
End
/*获取最大更新时间@param5天前所有数据，插入到临时表*/
declare 
@company_id uniqueidentifier;
select @company_id=UCML_OrganizeOID from dbo.UCML_Organize where Varchar1=@param1;

select  @sys_last_upd = convert(varchar(40),dateadd(day,@param5,max(SYS_LAST_UPD)),121) 
from dbo.Item where CompanyOID =@company_id  ;

if (@sys_last_upd is null) begin
		set @sys_last_upd='2011-06-09 23:08:16.623';

end

print('Item目标表最新更新时间-7天:');
print(@sys_last_upd);
/*准备临时表第1步，准备部分临时表字段，非必要字段在第4步添加*/
declare 
@sql_str1 Nvarchar(1000);

set @sql_str1 = 'select convert(uniqueidentifier,null) as ItemOID,ItemID,convert(uniqueidentifier,null) as CompanyOID,CompanyID,null) as BrandOID,BrandID,Item,Desc1,Desc2,RetPrice,Year,SeasonID,SeasonName,YearSeasonID,YearSeasonName,CateID1,CateName1,CateID2,CateName2,CateID3,CateName3,CateID4,CateName4,CateID5,CateName5,CateID6,CateName6,CateID7,CateName7,CateID8,CateName8,CateID9,CateName9,CateID10,CateName10,Remark 	
into ##tempItem 
from '+@linksrv+'.dbo.v_Intf_Item as v 
where v.SYS_LAST_UPD>'''+convert(varchar(40),@sys_last_upd,121)+''' and CompanyID= '''+@param1+'''';


exec (@sql_str1)

/*准备临时表第2步,并判断是否关联UCML_Organize表成功*/
update ##tempItem set CompanyOID=U.UCML_OrganizeOID
from ##tempItem as T,dbo.UCML_Organize as U
where T.CompanyID=U.Varchar1;
--异常处理代码
if exists( select * from ##tempItem where CompanyOID is null)
	RAISERROR (
				N'异常:关联UCML_Organize组织机构UCML表失败',16,-- 错误级别,任何用户都可以指定 0 到 18 之间的严重级别，[0,10]的闭区间内，不会跳到catch； 
				1	--如果是[11,19],则跳到catch；如果[20,无穷)，则直接终止数据库连接；
           ) ;    

--
/*准备临时表第3步,并判断是否关联Brand表成功*/
update ##tempItem set BrandOID=B.BrandOID 
from ##tempItem as T,dbo.Brand as B
where T.BrandID=B.Code;
--异常处理代码
if exists( select * from ##tempItem where BrandOID is null)
	RAISERROR (
				N'异常:关联Brand品牌表失败',-- 错误级别 
				1	
           ) ;    


/**********************准备Item临时表完成*********/


/**********************准备ItemSKU临时表开始******/
if object_id('tempdb.dbo.##tempItemSKU') is not null Begin
    drop table ##tempItemSKU
End
print('SKU延用Item目标表最新更新时间@param5天:');
print(@sys_last_upd);
/*准备临时表第1步,未包含ItemSKUOID字段 */
declare 
@sql_str2 Nvarchar(1000);
set @sql_str2 = 'select  convert(uniqueidentifier,V.ItemID,--需删除的字段
		V.Col_id,V.Siz_id,V.Log_id,V.Cost1,V.Cost2,V.Cost3,V.Cost4,V.Cost5,V.Cost6,V.Cost7,V.Cost8,V.Cost9,V.Cost10,V.TotCost
into ##tempItemSKU
from '+@linksrv+'.dbo.v_Intf_ItemSKU AS V where CompanyID='''+@param1+'''
AND  SYS_LAST_UPD>'''+convert(varchar(40),121)+'''';

exec (@sql_str2)


--
/**********************准备ItemSKU临时表完成******/

/**********************准备Location临时表开始*****/
if object_id('tempdb.dbo.##tempLocation') is not null Begin
    drop table ##tempLocation
End
print('Location延用Item目标表最新更新时间@param5天:');
print(@sys_last_upd);
/*准备临时表第1步*/
declare 
@sql_str3 Nvarchar(1000);
set @sql_str3 = 'select  LocID,--需删除的字段
		Name,Remark
into ##tempLocation
from '+@linksrv+'.dbo.v_Intf_Location 
where  SYS_LAST_UPD>'''+convert(varchar(40),121)+'''';
PRINT('从接口获取到的商店数量')
exec (@sql_str3)



/*准备临时表第2步,并判断是否关联UCML_Organize表成功*/
update ##tempLocation set CompanyOID=U.UCML_OrganizeOID
from ##tempLocation as T,dbo.UCML_Organize as U
where T.CompanyID=U.Varchar1;
--异常处理代码
if exists( select * from ##tempLocation where CompanyOID is null)
	RAISERROR (
				N'异常:关联UCML_Organize组织机构UCML表失败',10]的闭区间内，不会跳到catch； 
				2	--如果是[11,无穷)，则直接终止数据库连接；
           ) ;    
--

/**********************准备Location临时表结束*****/

/**********************准备临时表代码块结束************************************************/


BEGIN transaction

/**********************数据同步操作代码块************************************************/

/*更新已存在的款式*/
print('以下为更新款式操作数')
update dbo.Item set CompanyOID=T.CompanyOID,BrandOID=T.BrandOID,Item=T.Item,Desc1=T.Desc1,Desc2=T.Desc2,RetPrice=T.RetPrice,Year=T.Year,SeasonID=T.SeasonID,YearSeasonID=T.YearSeasonID,CateID1=T.CateID1,CateID2=T.CateID2,CateID3=T.CateID3,CateID4=T.CateID4,CateID5=T.CateID5,CateID6=T.CateID6,CateID7=T.CateID7,CateID8=T.CateID8,CateID9=T.CateID9,CateID10=T.CateID10,Remark=T.Remark,SYS_LAST_UPD=getdate()
from ##tempItem as T left join dbo.Item as I on T.ItemID=I.ItemID where I.ItemID is not null ; 
/*插入新增的款式*/ 
print('以下为新增款式操作数')
insert into dbo.Item (ItemOID,CompanyOID,BrandOID,Remark,SYS_ORG,SYS_CreatedBy,SYS_LAST_UPD_BY,SYS_Created,SYS_LAST_UPD)  
select  newid() as ItemOID,T.ItemID,T.CompanyOID,T.BrandOID,T.Item,T.Desc1,T.Desc2,T.RetPrice,T.Year,T.SeasonID,T.YearSeasonID,T.CateID1,T.CateID2,T.CateID3,T.CateID4,T.CateID5,T.CateID6,T.CateID7,T.CateID8,T.CateID9,T.CateID10,T.Remark,SYS_ORG=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000'),SYS_CreatedBy=convert(uniqueidentifier,SYS_LAST_UPD_BY=convert(uniqueidentifier,SYS_Created=getdate(),SYS_LAST_UPD=getdate()
from ##tempItem as T left join dbo.Item as I on T.ItemID=I.ItemID 
where i.ItemID is null;
print('新增款式完成');


update ##tempItemSKU set ItemOID=I.ItemOID
from ##tempItemSKU as S,dbo.Item as I
where S.ItemID=I.ItemID;

/*更新已存在的款式成本*/
print('以下为更新款式成本数');
update dbo.ItemSKU  set Col_id=T.Col_id,Siz_id=T.Siz_id,Log_id=T.Log_id,Cost1=T.Cost1,Cost2=T.Cost2,Cost3=T.Cost3,Cost4=T.Cost4,Cost5=T.Cost5,Cost6=T.Cost6,Cost7=T.Cost7,Cost8=T.Cost8,Cost9=T.Cost9,Cost10=T.Cost10,TotCost=T.TotCost
from ##tempItemSKU as T left join dbo.ItemSKU as I on T.ItemOID=I.ItemOID where I.ItemOID is not null ; 
/*插入新增的款式成本*/
print('以下为插入款式成本数');
insert into dbo.ItemSKU  
select  newid() as ItemSKUOID,T.ItemOID,T.Col_id,T.Siz_id,T.Log_id,T.Cost1,T.Cost2,T.Cost3,T.Cost4,T.Cost5,T.Cost6,T.Cost7,T.Cost8,T.Cost9,T.Cost10,T.TotCost
from ##tempItemSKU as T left join dbo.ItemSKU as I on T.ItemOID=I.ItemOID 
where I.ItemOID is null;
print('新增款式成本完成');
--select * from ##tempItemSKU


/*更新已存在的公司信息*/
print('以下为更新商店数')
update  dbo.Location  set LocID=T.LocID,Name=T.Name,Remark=T.Remark
from ##tempLocation as T inner join dbo.Location as I on T.CompanyOID=I.CompanyOID AND T.LocID=I.LocID ; 

/*插入新增的公司信息*/
print('以下为新增商店数')
insert into dbo.Location (LocationOID,LocID,Name,SYS_LAST_UPD)
select  newid() as LocationOID,T.LocID,T.Name,SYS_LAST_UPD=getdate()
from ##tempLocation as T left join dbo.Location as I on T.CompanyOID=I.CompanyOID AND t.LocID=I.LocID
where I.LocID is null;
print('新增公司信息完成');





/*插入新增的CRM.Item.Season代码值*/  
select CodeValueOID,CodeID,CodeName,CodeTableID,CodeClassifyOID into #t_p_s from CodeValue 
where CodeClassifyOID='00002d9c-0000-0000-0000-000000000000'
select SeasonID,SeasonName into #t_p_s1  from ##tempItem group by SeasonID,SeasonName


print('以下新增season信息');
insert into  CodeValue (CodeValueOID,CodeClassifyOID)   
select  newid() as CodeValueOID,T.SeasonID as CodeID,T.SeasonName as CodeValue,'CRM.Item.Season' as CodeTableID,'00002d9c-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_s1  as T 
	 left join #t_p_s as C on T.SeasonID=C.CodeID  
where C.CodeID  is null ; 
PRINT('222222222211118888888888888888888888')
print('以下更新season信息');
update  CodeValue set CodeName=T.SeasonName from CodeValue C
inner join #t_p_s1  as T 
on  T.SeasonID=C.CodeID WHERE C.CodeClassifyOID='00002d9c-0000-0000-0000-000000000000'

  



/*插入新增的CRM.Item.YearSeason代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_y from CodeValue 
where CodeClassifyOID='00002d9d-0000-0000-0000-000000000000'
select YearSeasonID,YearSeasonName  INTO #t_p_y1 from ##tempItem group by YearSeasonID,YearSeasonName
print('以下新增YearSeason信息');
insert into  CodeValue (CodeValueOID,T.YearSeasonID as CodeID,T.YearSeasonName as CodeValue,'CRM.Item.YearSeason' as CodeTableID,'00002d9d-0000-0000-0000-000000000000') as CodeClassifyOID   
from  #t_p_y1  as T  
	 left join #t_p_y as C on T.YearSeasonID=C.CodeID   
where C.CodeID  is null ;  
print('以下更新YearSeason信息');
update  CodeValue set CodeName=T.YearSeasonName from CodeValue C
inner join #t_p_y1  as T 
on  T.YearSeasonID=C.CodeID WHERE C.CodeClassifyOID='00002d9d-0000-0000-0000-000000000000'




  
/*插入新增的CRM.Item.Cate1代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_1 from CodeValue 
where CodeClassifyOID='00002d9e-0000-0000-0000-000000000000'
select CateID1,CateName1 INTO #t_p_11 from ##tempItem group by CateID1,CateName1
print('以下新增CRM.Item.Cate1信息');
insert into  CodeValue (CodeValueOID,T.CateID1 as CodeID,T.CateName1 as CodeValue,'CRM.Item.Cate1' as CodeTableID,'00002d9e-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_11  as T  
	left join #t_p_1 as C on T.CateID1=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate1信息');
update  CodeValue set CodeName=T.CateName1 from CodeValue C
inner join #t_p_11  as T 
on  T.CateID1=C.CodeID WHERE C.CodeClassifyOID='00002d9e-0000-0000-0000-000000000000'




  
/*插入新增的CRM.Item.Cate2代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_2 from CodeValue 
where CodeClassifyOID='00002d9f-0000-0000-0000-000000000000'
select CateID2,CateName2 INTO #t_p_21 from ##tempItem group by CateID2,CateName2
print('以下新增CRM.Item.Cate2信息');
insert into  CodeValue (CodeValueOID,T.CateID2 as CodeID,T.CateName2 as CodeValue,'CRM.Item.Cate2' as CodeTableID,'00002d9f-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_21  as T  
		left join #t_p_2 as C on T.CateID2=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate2信息');
update  CodeValue set CodeName=T.CateName2 from CodeValue C
inner join #t_p_21  as T 
on  T.CateID2=C.CodeID WHERE C.CodeClassifyOID='00002d9f-0000-0000-0000-000000000000'




  
/*插入新增的CRM.Item.Cate3代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_3 from CodeValue 
where CodeClassifyOID='00002da0-0000-0000-0000-000000000000'
select CateID3,CateName3 INTO #t_p_31 from ##tempItem group by CateID3,CateName3
print('以下新增CRM.Item.Cate3信息');
insert into  CodeValue (CodeValueOID,T.CateID3 as CodeID,T.CateName3 as CodeValue,'CRM.Item.Cate3' as CodeTableID,'00002da0-0000-0000-0000-000000000000') as CodeClassifyOID   
from  #t_p_31 as T  
	left join #t_p_3 as C on T.CateID3=C.CodeID   
where C.CodeID  is null ;
print('以下更新CRM.Item.Cate3信息');
update  CodeValue set CodeName=T.CateName3 from CodeValue C
inner join #t_p_31  as T 
on  T.CateID3=C.CodeID WHERE C.CodeClassifyOID='00002da0-0000-0000-0000-000000000000'




  
  
/*插入新增的CRM.Item.Cate4代码值*/ 
select CodeValueOID,CodeClassifyOID into #t_p_4 from CodeValue 
where CodeClassifyOID='00002da1-0000-0000-0000-000000000000'
select CateID4,CateName4 INTO  #t_p_41 from ##tempItem group by CateID4,CateName4
 print('以下新增CRM.Item.Cate4信息');
insert into  CodeValue (CodeValueOID,T.CateID4 as CodeID,T.CateName4 as CodeValue,'CRM.Item.Cate4' as CodeTableID,'00002da1-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_41  as T  
left join #t_p_4 as C on T.CateID4=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate4信息');
update  CodeValue set CodeName=T.CateName4 from CodeValue C
inner join #t_p_41  as T 
on  T.CateID4=C.CodeID WHERE C.CodeClassifyOID='00002da1-0000-0000-0000-000000000000'





  
/*插入新增的CRM.Item.Cate5代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_5 from CodeValue 
where CodeClassifyOID='00002da2-0000-0000-0000-000000000000'
select CateID5,CateName5 INTO  #t_p_51 from ##tempItem group by CateID5,CateName5
 print('以下新增CRM.Item.Cate5信息');

insert into  CodeValue (CodeValueOID,T.CateID5 as CodeID,T.CateName5 as CodeValue,'CRM.Item.Cate5' as CodeTableID,'00002da2-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_51  as T  
	left join #t_p_5 as C on T.CateID5=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate5信息');
update  CodeValue set CodeName=T.CateName5 from CodeValue C
inner join #t_p_51  as T 
on  T.CateID5=C.CodeID WHERE C.CodeClassifyOID='00002da2-0000-0000-0000-000000000000'




  
/*插入新增的CRM.Item.Cate6代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_6 from CodeValue 
where CodeClassifyOID='00002da3-0000-0000-0000-000000000000'
select CateID6,CateName6 INTO #t_p_61 from ##tempItem group by CateID6,CateName6

 print('以下新增CRM.Item.Cate6信息');
insert into  CodeValue (CodeValueOID,T.CateID6 as CodeID,T.CateName6 as CodeValue,'CRM.Item.Cate6' as CodeTableID,'00002da3-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_61  as T  
	left join #t_p_6 as C on T.CateID6=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate6信息');

update  CodeValue set CodeName=T.CateName6 from CodeValue C
inner join #t_p_61  as T 
on  T.CateID6=C.CodeID WHERE C.CodeClassifyOID='00002da3-0000-0000-0000-000000000000'


  


/*插入新增的CRM.Item.Cate7代码值*/
select CodeValueOID,CodeClassifyOID into #t_p_7 from CodeValue 
where CodeClassifyOID='00002da4-0000-0000-0000-000000000000'
select CateID7,CateName7 INTO #t_p_71 from ##tempItem group by CateID7,CateName7
   print('以下新增CRM.Item.Cate7信息');
insert into  CodeValue (CodeValueOID,T.CateID7 as CodeID,T.CateName7 as CodeValue,'CRM.Item.Cate7' as CodeTableID,'00002da4-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_71  as T 
	 left join #t_p_7 as C on T.CateID7=C.CodeID   
where C.CodeID  is null ;  

print('以下更新CRM.Item.Cate7信息');
update  CodeValue set CodeName=T.CateName7 from CodeValue C
inner join #t_p_71  as T 
on  T.CateID7=C.CodeID WHERE C.CodeClassifyOID='00002da4-0000-0000-0000-000000000000'



  
/*插入新增的CRM.Item.Cate8代码值*/ 
select CodeValueOID,CodeClassifyOID into #t_p_8 from CodeValue 
where CodeClassifyOID='00002da5-0000-0000-0000-000000000000'
select CateID8,CateName8 INTO #t_p_81 from ##tempItem group by CateID8,CateName8
    print('以下新增CRM.Item.Cate8信息');
insert into  CodeValue (CodeValueOID,T.CateID8 as CodeID,T.CateName8 as CodeValue,'CRM.Item.Cate8' as CodeTableID,'00002da5-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_81  as T  
	left join #t_p_8 as C on T.CateID8=C.CodeID   
where C.CodeID  is null ; 
print('以下更新CRM.Item.Cate8信息');
 
update  CodeValue set CodeName=T.CateName8 from CodeValue C
inner join #t_p_81  as T 
on  T.CateID8=C.CodeID WHERE C.CodeClassifyOID='00002da5-0000-0000-0000-000000000000'



  
/*插入新增的CRM.Item.Cate9代码值*/ 
select CodeValueOID,CodeClassifyOID into #t_p_9 from CodeValue 
where CodeClassifyOID='00002da6-0000-0000-0000-000000000000'
select CateID9,CateName9 INTO  #t_p_91 from ##tempItem group by CateID9,CateName9

print('以下新增CRM.Item.Cate9信息');
insert into  CodeValue (CodeValueOID,T.CateID9 as CodeID,T.CateName9 as CodeValue,'CRM.Item.Cate9' as CodeTableID,'00002da6-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_91  as T 
	 left join #t_p_9 as C on T.CateID9=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate9信息');
update  CodeValue set CodeName=T.CateName9 from CodeValue C
inner join #t_p_91  as T 
on  T.CateID9=C.CodeID WHERE C.CodeClassifyOID='00002da6-0000-0000-0000-000000000000'




  
  
/*插入新增的CRM.Item.Cate10代码值*/  
select CodeValueOID,CodeClassifyOID into #t_p_10 from CodeValue 
where CodeClassifyOID='00002da7-0000-0000-0000-000000000000'
select CateID10,CateName10  INTO #t_p_101 from ##tempItem group by CateID10,CateName10
print('以下新增CRM.Item.Cate10信息');

insert into  CodeValue (CodeValueOID,T.CateID10 as CodeID,T.CateName10 as CodeValue,'CRM.Item.Cate10' as CodeTableID,'00002da7-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_p_101  as T 
	 left join #t_p_10 as C on T.CateID10=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Item.Cate10信息');
update  CodeValue set CodeName=T.CateName10 from CodeValue C
inner join #t_p_101  as T 
on  T.CateID10=C.CodeID WHERE C.CodeClassifyOID='00002da7-0000-0000-0000-000000000000'





  
/*插入新增的CRM.Loc.Cate1代码值*/ 
select CodeValueOID,CodeClassifyOID into #t_c_1 from CodeValue 
where CodeClassifyOID='00002dad-0000-0000-0000-000000000000'
select CateID1,CateName1 INTO #t_c_11  from ##tempLocation group by CateID1,CateName1
 print('以下新增CRM.Loc.Cate1信息');
insert into  CodeValue (CodeValueOID,'CRM.Loc.Cate1' as CodeTableID,'00002dad-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_c_11  as T 
	 left join #t_c_1 as C on T.CateID1=C.CodeID   
where C.CodeID  is null ; 
 print('以下更新CRM.Loc.Cate1信息');
update  CodeValue set CodeName=T.CateName1 from CodeValue C
inner join #t_c_11  as T 
on  T.CateID1=C.CodeID WHERE C.CodeClassifyOID='00002dad-0000-0000-0000-000000000000'




  
  
/*插入新增的CRM.Loc.Cate2代码值*/
select CodeValueOID,CodeClassifyOID into #t_c_2 from CodeValue 
where CodeClassifyOID='00002dae-0000-0000-0000-000000000000'
select CateID2,CateName2 INTO #t_c_21  from ##tempLocation group by CateID2,CateName2

 print('以下新增CRM.Loc.Cate2信息');
insert into  CodeValue (CodeValueOID,'CRM.Loc.Cate2' as CodeTableID,'00002dae-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_c_21  as T 
	 left join #t_c_2 as C on T.CateID2=C.CodeID   
where C.CodeID  is null ;  
print('以下更新CRM.Loc.Cate2信息');
update  CodeValue set CodeName=T.CateName2 from CodeValue C
inner join #t_c_21  as T 
on  T.CateID2=C.CodeID WHERE C.CodeClassifyOID='00002dae-0000-0000-0000-000000000000'
  


  
/*插入新增的CRM.Loc.Cate3代码值*/  
select CodeValueOID,CodeClassifyOID into #t_c_3 from CodeValue 
where CodeClassifyOID='00002daf-0000-0000-0000-000000000000'
select CateID3,CateName3 INTO #t_c_31 from ##tempLocation group by CateID3,CateName3
print('以下新增CRM.Loc.Cate3信息');
insert into  CodeValue (CodeValueOID,'CRM.Loc.Cate3' as CodeTableID,'00002daf-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_c_31  as T 
	 left join #t_c_3 as C on T.CateID3=C.CodeID   
where C.CodeID  is null ;  
  print('以下更新CRM.Loc.Cate3信息');

update  CodeValue set CodeName=T.CateName3 from CodeValue C
inner join #t_c_31  as T 
on  T.CateID3=C.CodeID WHERE C.CodeClassifyOID='00002daf-0000-0000-0000-000000000000'
  



  
/*插入新增的CRM.Loc.Cate4代码值*/
select CodeValueOID,CodeClassifyOID into #t_c_4 from CodeValue 
where CodeClassifyOID='00002db0-0000-0000-0000-000000000000'
select CateID4,CateName4 INTO #t_c_41 from ##tempLocation group by CateID4,CateName4
  print('以下新增CRM.Loc.Cate4信息');
insert into  CodeValue (CodeValueOID,'CRM.Loc.Cate4' as CodeTableID,'00002db0-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_c_41  as T
	  left join #t_c_4 as C on T.CateID4=C.CodeID   
where C.CodeID  is null ;  
  print('以下更新CRM.Loc.Cate4信息');
update  CodeValue set CodeName=T.CateName4 from CodeValue C
inner join #t_c_41  as T 
on  T.CateID4=C.CodeID WHERE C.CodeClassifyOID='00002db0-0000-0000-0000-000000000000'
  

  
  
/*插入新增的CRM.Loc.Cate5代码值*/  
select CodeValueOID,CodeClassifyOID into #t_c_5 from CodeValue 
where CodeClassifyOID='00002db1-0000-0000-0000-000000000000'
select CateID5,CateName5 INTO #t_c_51  from ##tempLocation group by CateID5,CateName5
  print('以下新增CRM.Loc.Cate5信息');
insert into  CodeValue (CodeValueOID,'CRM.Loc.Cate5' as CodeTableID,'00002db1-0000-0000-0000-000000000000') as CodeClassifyOID   
from #t_c_51  as T  
	left join #t_c_5 as C on T.CateID5=C.CodeID   
where C.CodeID  is null ;  
  print('以下更新CRM.Loc.Cate5信息');
update  CodeValue set CodeName=T.CateName5 from CodeValue C
inner join #t_c_51  as T 
on  T.CateID5=C.CodeID WHERE C.CodeClassifyOID='00002db1-0000-0000-0000-000000000000'
  





/*回写日志*/
print('回写日志完成');
insert into CRMData_Test.dbo.SYS_UCML_LOG(SYS_UCML_LOGOID,SYS_DATE,ActionName,LogType,Url,UserHostAddress,UserHostName,BusinessName,UCML_User_FK,UCML_Post_FK,UCML_Division_FK)
				values(newid(),GETDATE(),'數據同步','',host_name(),'基礎數據同步','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000');
/*提交事务*/
commit transaction
end try
BEGIN CATCH
	ROLLBACK transaction
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,-- Message text.
               @ErrorSeverity,-- Severity.
               @ErrorState     -- State.
               );

END CATCH;
go


exec sp_BaseSync 'A',null,-7

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同