CODEWITHSUNDEEP
sql-server-2008
MadHenchbot
MadHenchbot

Reputation: 1385

SQL Server 2008 - Avoiding a foreach loop

I am trying desperately to avoid a foreach situation in SQL Server 2008 (my background is in c#).

Basically, I have a list of SKUs. For each SKU in the list, I need to perform some calculations that determine if that particular SKU will be displayed on the web.

To get my list of SKUs, I use this:

SELECT Feed.StyleCode as SKU
FROM [eCommerce].[dbo].[BABW_ItemFeed] as Feed
WHERE Feed.ProductDefinition = 'Kit'

Returning this:

enter image description here

And to calculate each SKUs fields, I've been using this:

DECLARE @SKU AS varchar(50)
SET @SKU= '11993_16559_16227'
SELECT
    @SKU as SKU,
    0 AS Quantity,
    MIN(ISNULL(Sending.IsActive, 'WEBNO')) AS IsActive, 
    MAX(ISNULL(Sending.IsDiscontinued, 1)) AS IsDiscontinued
FROM
( 
    SELECT * FROM [eCommerce].[dbo].[Split] (
      @SKU
      ,'_') 
) AS SplitSkus 
LEFT JOIN #SkusToSend AS Sending
    ON Sending.SKU = SplitSkus.items

Returning this:

enter image description here

Now I need to synch the two tables together, removing the @SKU declaration. I don't think I'm able to use a UNION to do this, because the second function requires fore-knowledge of the SKU it will be processing... and a JOIN would require something to join on, which I don't really have. Is there some function I'm not familiar with that I can use to create a complete table of SKUs in one go without a looping mechanism?

Upvotes: 3

Views: 852

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Try a CROSS APPLY... which will execute your UDF for each row in BABW_ItemFeed:

SELECT
    Feed.StyleCode as SKU,
    COUNT(*) AS Quantity,
    MIN(ISNULL(Sending.IsActive, 'WEBNO')) AS IsActive, 
    MAX(ISNULL(Sending.IsDiscontinued, 1)) AS IsDiscontinued
FROM
    [eCommerce].[dbo].[BABW_ItemFeed] as Feed 
    CROSS APPLY [eCommerce].[dbo].[Split] (Feed.StyleCode, '_') AS SplitSkus 
    LEFT JOIN #SkusToSend AS Sending
        ON Sending.SKU = SplitSkus.items
WHERE
    Feed.ProductDefinition = 'Kit'
GROUP BY
    Feed.StyleCode

Upvotes: 4

Chains
Chains

Reputation: 13157

Stop using Min() and Max()...or else, pull SKU (don't use the parameter in the SELECT).

Try This:

SELECT
    SKU,
    0 AS Quantity,
    MIN(ISNULL(Sending.IsActive, 'WEBNO')) AS IsActive, 
    MAX(ISNULL(Sending.IsDiscontinued, 1)) AS IsDiscontinued
FROM
( 
SELECT [eCommerce].[dbo].[Split] (Feed.StyleCode,'_')  as SKU
FROM [eCommerce].[dbo].[BABW_ItemFeed] as Feed
WHERE Feed.ProductDefinition = 'Kit'
) AS SplitSkus 
LEFT JOIN #SkusToSend AS Sending
    ON Sending.SKU = SplitSkus.items

Upvotes: 0

Related Questions