Click here to Skip to main content
15,888,113 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I work on SQL server 2012 

I have stored procedure as below 

the stored procedure delete from two tables tradecode and tradecodecontrol

i forget to get column from excel sheet so it is continue execution and delete from database  although it have error on first block when get data from input file

so i need before delete stop execution if stored procedure have error execution before delete 

so what i do 

mu delete stored procedure as below :


What I have tried:

SQL
ALTER PROC [dbo].[sp_DeleteTradeCodesByPlImporter] 

@ImportFilePath varchar(500) ,
@ExportFilePath varchar(500)





AS

BEGIN
create table #TempPC 
(
[ID] INT IDENTITY,
ZPL NVARCHAR(300) NOT NULL,
ZPLID INT,
CodeType nvarchar(100),
CodeTypeId int,
[Status] NVARCHAR(250)
)

DECLARE @sql NVARCHAR(MAX)='INSERT into #TempPC 
( 
ZPL,
CodeType
)
select ZPL,CodeType FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0 Xml;HDR=YES;Database=' + @ImportFilePath + ''',''SELECT ZPL,CodeType FROM [Sheet1$]'')' 

EXEC(@sql)
Declare @CreatedBy int = (select InsertedBy from ImporterQueue where filepath = @ImportFilePath);


UPDATE t SET t.ZPLID=TCDC.ZPLID
FROM #TempPC t 
INNER JOIN dbo.Nop_AcceptedValuesOption AVO ON AVO.AcceptedValuesID=110 AND t.ZPL=AVO.Name 
LEFT OUTER JOIN Parts.TradeCodeControl TCDC ON AVO.AcceptedValuesOptionID=TCDC.ZPLID 



UPDATE t SET t.[Status]='Not Existing In the Table To be Deleted'
FROM #TempPC t WHERE t.ZPLID IS NULL

UPDATE t SET t.CodeTypeId=TCDC.CodeTypeID
FROM #TempPC t 
INNER JOIN dbo.Nop_AcceptedValuesOption AVO ON AVO.AcceptedValuesID=5652 AND t.CodeType=AVO.Name 
LEFT OUTER JOIN Parts.TradeCodeControl TCDC ON AVO.AcceptedValuesOptionID=TCDC.CodeTypeID 
// here i need to stop stored procedure execution if it have error before


DELETE TCC FROM #TempPC t INNER JOIN Parts.TradeCodes TCC ON t.ZPLID=TCC.ZPLID where  ((t.CodeTypeId IS NULL ) OR TCC.CodeTypeID=t.CodeTypeId) AND t.Status is null

DELETE TCC FROM #TempPC t INNER JOIN Parts.TradeCodeControl TCC ON t.ZPLID=TCC.ZPLID where  ((t.CodeTypeId IS NULL) OR TCC.CodeTypeID=t.CodeTypeId) AND t.Status is null





DECLARE @Import NVARCHAR(max)= 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;HDR=YES;Database='
+@ExportFilePath + ''',''SELECT ZPL,CodeType,Status FROM [Sheet1$]'') select 
ZPL,CodeType , [Status] from #TempPC'



EXEC(@Import) 

end
Posted
Updated 29-Jan-20 23:00pm

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900