May 12, 2014 at 10:16 am
UPDATE rs_current_runtime
SET processed_date = NULL
WHERE CORP_PRFT_CTR IN
(SELECT LOC_NUM
FROM RET_LOC_INFO
WHERE LIVE_IN_PRICEBOOK = 'Y')
GO
WHILE (SELECT COUNT(*) FROM rs_current_runtime
WHERE processed_date IS NULL) > 0
BEGIN
DECLARE @CurCorpPrftCtr int
SET @CurCorpPrftCtr = (SELECT MIN(a.corp_prft_ctr)
FROM rs_current_runtime a
WHERE a.processed_date IS NULL
AND a.is_updated = (SELECT MIN(b.is_updated)
FROM rs_current_runtime b
WHERE b.processed_date IS NULL))
EXEC[dbo].[CalculateEffectiveRetail]
@CorpProfitCenter = @CurCorpPrftCtr,
@ProcessPriceChangeRequired = 1
UPDATE rs_current_runtime
SET processed_date = GETDATE()
WHERE corp_prft_ctr = @CurCorpPrftCtr
END
GO
May 12, 2014 at 10:57 am
Which of the subqueries are you having a problem with?
May 12, 2014 at 12:24 pm
We could probably look at reworking this entirely so that it is set based instead inside a while loop. That would increase the performance greatly and would eliminate all the additional checking for each row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 12, 2014 at 1:14 pm
The query you posted shouldn't give an error on a subquery returning more than 1 value. However, we can't see the stored procedure. Your error might be in there.
I agree with Sean on working over the complete rewrite.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply