Need help with Store Procedure

1

The Code below works fine for my procedure. But I need to make an addition.
First is the StockCode and Price Code exist in companyB.dbo.invPrice and EndDate < CurrentDate I need to delete this record from companyB,invPrice and update ImportedPromoPricing.Completed to D

Second if I add a record from ImportedPromoPricing then I need to update ImportedPromoPricing.Completed to Y

Initial procedure is below.

begin

insert into companyB.dbo.InvPrice ([invprice].[StockCode],[invprice].[PriceCode],[invprice].[SellingPrice])

select ImportedPromoPricing.[StockCode],ImportedPromoPricing.[PriceCode],ImportedPromoPricing.[SellingPrice]
from ImportedPromoPricing
left join companyB.dbo.InvPrice on ImportedPromoPricing.StockCode = companyB.dbo.InvPrice.StockCode and ImportedPromoPricing.PriceCode = companyB.dbo.InvPrice.PriceCode
where companyB.dbo.InvPrice.StockCode is null and ImportedPromoPricing.[StartDate]<= Convert(datetime, Convert(int, GetDate())) and
(ImportedPromoPricing.[EndDate] >= Convert(datetime, Convert(int, GetDate())) or ImportedPromoPricing.[EndDate] is null)

update companyB.dbo.InvPrice
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join companyB.dbo.InvPrice on ImportedPromoPricing.StockCode = companyB.dbo.InvPrice.StockCode and ImportedPromoPricing.PriceCode = companyB.dbo.InvPrice.PriceCode
where companyB.dbo.InvPrice.StockCode is not null
end

2

Strongly recommend that you use Synonym as a replacement for all 3-part naming. If (and WHEN!) the database name changes it is a doddle to change all the Synonyms, rather than all the code ...

presume you are wanting to convert GetDate() to a DATE. You could just cast it to DATE instead:

Convert(DATE, GetDate())

in particular if your StartDate and EndDate columns are DATE rather than DATETIME. Might be splitting hairs ... but I think it would be a tad more efficient.

I would recommend that you encapsulate your INSERT and UPDATE into a transaction - so that one cannot happen without the other, and the JOIN /WHERE for the UPDATE won't find different rows to the INSERT (e.g. if someone else changed something between the INSERT and the UPDATE.

Would it be worthwhile using a TRIGGER for this? Then if ANY process makes a change that qualifies then the trigger can make the associated data changes.

If not then, personally, I would get the details [of all matching rows] into a temporary table first, with their PKeys, and then make a number of INSERT / UPDATE / DELETE statements to the relevant tables (all WITHIN a Transaction).

You could have a column(s), in your #TEMP table, indicating what Action(s) were required - with respect to the various Insert / Update / Delete actions that are available in the code.