不带参数的存储过程
-- =============================================--不参数的存储过程-- =============================================if(exists (select * from sys.objects where name = 'sp_BaseSyncWithoutParam')) drop procedure sp_BaseSyncWithoutParamgocreate procedure sp_BaseSyncWithoutParamas select * from tbl_test;goexec sp_BaseSyncWithoutParam;
带参数的存储过程
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')) DROP PROCEDURE sp_BaseSyncgoCREATE PROCEDURE sp_BaseSync @ID int,@name varchar(20) outputASinsert 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;goselect * from tbl_test
带通配符的存储过程
-- =============================================--带参数的存储过程-- =============================================IF(EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_BaseSync')) DROP PROCEDURE sp_BaseSyncgoCREATE PROCEDURE sp_BaseSync @ID int,@name varchar(20) outputAS 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;gocreate proc sp_BaseSyncWithCursorasdeclare @ID int,@name varchar(20);declare resultSet cursor for select * from tbl_testopen resultSetfetch next from resultSet into @ID,@name; print @@fetch_statuswhile @@fetch_status=0beginupdate tbl_test set name='txm' where ID=@ID;fetch next from resultSet into @ID,@name;endclose resultSet;deallocate resultSetgoexec sp_BaseSyncWithCursor
-- =============================================
--使用分页的存储过程
-- =============================================
if(exists (select * from sys.objects where name= 'pro_page')) drop proc pro_page;create proc pro_page @startIndex int,@endindex intas 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 @endindexgo--drop proc pro_pageexec pro_page 1,6
USE [CRMData_Test]GO/****** Object: StoredProcedure [dbo].[sp_BaseSync] Script Date: 07/17/2015 17:03:50 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGOif(exists (select * from sys.objects where name= 'sp_BaseSync')) drop proc sp_BaseSyncgocreate proc [dbo].[sp_BaseSync] @param1 varchar(1000),--GROUP_ID @param2 varchar(1000),--NulL @param3 varchar(1000),--NulL @param4 varchar(1000),--NulL @param5 int --date diffas/**********************准备临时表代码块开始*************************************************/begin try--get server locationdeclare @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 ##tempItemEnd/*获取最大更新时间@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';endprint('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_OrganizeOIDfrom ##tempItem as T,dbo.UCML_Organize as Uwhere 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 Bwhere 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 ##tempItemSKUEndprint('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.TotCostinto ##tempItemSKUfrom '+@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 ##tempLocationEndprint('Location延用Item目标表最新更新时间@param5天:');print(@sys_last_upd);/*准备临时表第1步*/declare @sql_str3 Nvarchar(1000);set @sql_str3 = 'select LociD,--需删除的字段 name,Remarkinto ##tempLocationfrom '+@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_OrganizeOIDfrom ##tempLocation as T,dbo.UCML_Organize as Uwhere 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.ItemOIDfrom ##tempItemSKU as S,dbo.Item as Iwhere 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.TotCostfrom ##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.TotCostfrom ##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.Remarkfrom ##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.LociDwhere 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,Seasonnameprint('以下新增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('111111111111118888888888888888888888')print('以下更新season信息');update CodeValue set Codename=T.Seasonname from CodeValue Cinner 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,YearSeasonnameprint('以下新增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 Cinner 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,Catename1print('以下新增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 Cinner 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,Catename2print('以下新增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 Cinner 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,Catename3print('以下新增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 Cinner 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 Cinner 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 Cinner 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 Cinner 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 Cinner 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 Cinner 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,Catename9print('以下新增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 Cinner 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,Catename10print('以下新增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 Cinner 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 Cinner 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 Cinner 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,Catename3print('以下新增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 Cinner 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 Cinner 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 Cinner 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 transactionend tryBEGIN 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;goexec sp_BaseSync 'A',null,-7 总结 以上是内存溢出为你收集整理的sqlserver存储过程笔记全部内容，希望文章能够帮你解决sqlserver存储过程笔记所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）