SQL Update - Is there a more elegant and efficient way of doing this?

I have 3 tables Site, Price and PriceMonth. Site has many Prices (joined on siteId). PriceMonth holds a date which is a looked up by Price (joined on PriceMonth).

The idea is that the price records are created when a site is created as place holders.

When a user clicks a Price row in a view - I need to update the prices for the rest of that year only and from that month only.

I've written code for a stored procedure which works. Please ignore the hard coded values for now. It works but can it be made simpler and more efficient?

Code:

DECLARE @startDate smallDateTime                  
DECLARE @roc decimal(5,2)
DECLARE @Lec decimal(5,2)
DECLARE @power decimal(5,2)

SET @roc =  (SELECT roc FROM [Price] WHERE siteId = 77 AND PriceMonthId = 527)
SET @lec = (SELECT lec FROM [P开发者_运维百科rice] WHERE siteId = 77 AND PriceMonthId = 527)   
SET @power = (SELECT [power] FROM [Price] WHERE siteId = 77 AND PriceMonthId = 527) 
SET @startDate = (Select [month] FROM  [PriceMonth] WHERE PriceMonthId = 527) 

UPDATE 
    Price
SET 
    roc = @roc
,   lec = @lec
,   [power] = @power
FROM 
    Price
    INNER JOIN priceMonth pm ON price.priceMonthId = pm.priceMonthId

WHERE
   (DATEPART(mm,pm.[Month]) > DATEPART(mm,@startDate)   AND 
   (DATEPART(yy,pm.[Month]) = DATEPART(yy,@startDate))) AND
    price.SiteId = 77 

---

You can add the variables as joins to the query as follows:

UPDATE     p
SET         roc = sourcePrice.roc,   
            lec = sourcePrice.lec,   
            [power] = sourcePrice.[power]
FROM        Price p
            INNER JOIN [Price] sourcePrice
            on p.siteId = sourcePrice.siteId
            and sourcePrice.siteId = 527
            INNER JOIN priceMonth pm 
            ON price.priceMonthId = pm.priceMonthId
            INNER JOIN priceMonth sourcepm
            ON sourcepm.PriceMonthId = 527

WHERE   pm.[Month] > sourcepm.StartDate
AND     (DATEPART(yy,pm.[Month]) = DATEPART(yy,sourcepm.StartDate))) 
AND     price.SiteId = 77

Also note that I have removed the function from one of your date comparisons - this is so that SQL Server can use any index that might be defined on there to at least narrow the range of values.

---

You could use multiple assignment to set many variables with the results of one query.

SELECT top 1
  @roc = roc,
  @lec = lec,
  @power = power
FROM Price
WHERE siteId = 77 and pricemonthid = 527

Watch out with this technique:

• if no rows are returned, the variables remain unchanged (null in this case).
• if multiple rows are return, each row is assigned - which leaves the last set of values as the final values. If no ordering was specified, then sql server determines the order of the rows and any row could be last.

---

You could pre-compute the date range that you want to update. This could allow an index on dates to be useful.

DECLARE @StartRange datetime, @EndRange datetime

SET @StartRange = DateAdd(mm, DateDiff(mm, @startDate, 0), 0)
SET @EndRange = DateAdd(yy, 1 + DateDiff(yy, @StartRange, 0), 0)

UPDATE...
WHERE @StartRange <= pm.Month AND pm.Month < @EndRange
  AND price.SiteId = 77

---

Regarding the requirement for placeholders, you can always use a LEFT JOIN and COALESCE to be able to fall back to the previous month's price. Alternatively, schedule a task to create the new month's prices from the previous month at midnight on the last day of each month.