sqlserver存储过程笔记

最新推荐文章于 2022-06-09 22:50:17 发布

巫巫巫政霖

最新推荐文章于 2022-06-09 22:50:17 发布

阅读量711

点赞数

文章标签：存储过程 sqlserver 巫政霖

本文链接：https://blog.csdn.net/Doraemon_wu/article/details/46444019

版权

不带参数的存储过程

-- =============================================
--不参数的存储过程
-- =============================================
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,
	    convert(uniqueidentifier,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品牌表失败',
				16, -- 错误级别 
				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,null) as ItemOID, 
		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),@sys_last_upd,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,
		convert(uniqueidentifier,null) as CompanyOID, 
		CompanyID, --需删除的字段
		Name, CateID1,CateName1, CateID2,CateName2, CateID3,CateName3, CateID4,CateName4, CateID5,CateName5,
		Remark
into ##tempLocation
from '+@linksrv+'.dbo.v_Intf_Location 
where  SYS_LAST_UPD>'''+convert(varchar(40),@sys_last_upd,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表失败',
				16, -- 错误级别, 任何用户都可以指定 0 到 18 之间的严重级别，[0,10]的闭区间内，不会跳到catch； 
				2	--如果是[11,19],则跳到catch；如果[20,无穷)，则直接终止数据库连接；
           ) ;    
--

/**********************准备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,ItemID,CompanyOID,BrandOID,Item,Desc1,Desc2,RetPrice,Year,SeasonID,YearSeasonID,
			CateID1,CateID2,CateID3,CateID4,CateID5,CateID6,CateID7,CateID8,CateID9,CateID10,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,'00000000-0000-0000-0000-000000000000'),
		SYS_LAST_UPD_BY=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000'),
		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, 
	    CateID1=T.CateID1, CateID2=T.CateID2, CateID3=T.CateID3, CateID4=T.CateID4, CateID5=T.CateID5,
		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,CompanyOID,Name,CateID1,CateID2,CateID3,CateID4,CateID5,
							Remark,SYS_ORG,SYS_CreatedBy,SYS_LAST_UPD_BY,SYS_Created,SYS_LAST_UPD)
select  newid() as LocationOID, T.LocID, T.CompanyOID, T.Name, 
		T.CateID1, T.CateID2, T.CateID3, T.CateID4, T.CateID5, T.Remark,
		SYS_ORG=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000'),
		SYS_CreatedBy=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000'),
		SYS_LAST_UPD_BY=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000'),
		SYS_Created=getdate(),
		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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.SeasonID as CodeID, T.SeasonName as CodeValue , 'CRM.Item.Season' as CodeTableID,  
        convert(uniqueidentifier,'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('111111111111118888888888888888888888')
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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.YearSeasonID as CodeID, T.YearSeasonName as CodeValue , 'CRM.Item.YearSeason' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID1 as CodeID, T.CateName1 as CodeValue , 'CRM.Item.Cate1' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID2 as CodeID, T.CateName2 as CodeValue , 'CRM.Item.Cate2' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID3 as CodeID, T.CateName3 as CodeValue , 'CRM.Item.Cate3' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID4 as CodeID, T.CateName4 as CodeValue , 'CRM.Item.Cate4' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID5 as CodeID, T.CateName5 as CodeValue , 'CRM.Item.Cate5' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID6 as CodeID, T.CateName6 as CodeValue , 'CRM.Item.Cate6' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID7 as CodeID, T.CateName7 as CodeValue , 'CRM.Item.Cate7' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID8 as CodeID, T.CateName8 as CodeValue , 'CRM.Item.Cate8' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID9 as CodeID, T.CateName9 as CodeValue , 'CRM.Item.Cate9' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID10 as CodeID, T.CateName10 as CodeValue , 'CRM.Item.Cate10' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID1 as CodeID, T.CateName1 as CodeValue , 'CRM.Loc.Cate1' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID2 as CodeID, T.CateName2 as CodeValue , 'CRM.Loc.Cate2' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID3 as CodeID, T.CateName3 as CodeValue , 'CRM.Loc.Cate3' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID4 as CodeID, T.CateName4 as CodeValue , 'CRM.Loc.Cate4' as CodeTableID,  
        convert(uniqueidentifier,'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, CodeID, CodeName, CodeTableID, 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, CodeID, CodeName, CodeTableID, CodeClassifyOID)   
select  newid() as CodeValueOID, T.CateID5 as CodeID, T.CateName5 as CodeValue , 'CRM.Loc.Cate5' as CodeTableID,  
        convert(uniqueidentifier,'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(), '數據同步', 0, '','', host_name(), '基礎數據同步', 
						'00000000-0000-0000-0000-000000000000', '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,null,null,-7

巫巫巫政霖

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sqlserver存储过程笔记

SET ANSI_NULLS ON --允许比较运算符返回true or falseGOSET QUOTED_IDENTIFIER ON --当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔GO IF(EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_BaseSync'))
复制链接

扫一扫