write stored proc for the two queries, truncating and inserting two different tables.

Question

Hi All,

I have two queries below. how do i put them in one stored proc. both the queries are truncating and inserting records in two different tables.

QUERY 1

truncate table [PlanFinder].[InvalidAwps] go

INSERT INTO [PlanFinder].[InvalidAwps]

(Ndc, AwpUnitCost) 

SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost 

FROM 

    PlanFinder.PlanFinder.HpmsFormulary P 

    LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices  

               WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A 

ON P.Ndc = A.Ndc  

WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL) 

AND p.Ndc IS NOT NULL

---

Query 2

truncate table [PlanFinder].[MissingNdcs] go

INSERT INTO [PlanFinder].[MissingNdcs]

(Ndc) 

SELECT DISTINCT Ndc 

FROM 

    PlanFinder.InvalidAwps   

WHERE AwpUnitCost IS NULL  

Thanks and Appreciate any help

Answer 1

take out the GO statements and dump all the code in 1 proc

create procedure prBla
as

truncate table [PlanFinder].[InvalidAwps] 

INSERT INTO [PlanFinder].[InvalidAwps](Ndc, AwpUnitCost) 

SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost 
FROM 
    PlanFinder.PlanFinder.HpmsFormulary P 
    LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices  
               WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A 
ON P.Ndc = A.Ndc  
WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL) 
AND p.Ndc IS NOT NULL

truncate table [PlanFinder].[MissingNdcs] 
INSERT INTO [PlanFinder].[MissingNdcs] (Ndc) 

SELECT DISTINCT Ndc 
FROM  PlanFinder.InvalidAwps   
WHERE AwpUnitCost IS NULL  
GO

you might want to add some error handling...what do you want to do...rollback all if one of the statements fails or not?