Create temp table while looping through cursor??

Posted on 2004-10-08
Last Modified: 2010-05-18
Hi There,
I am trying to view a recordset from a temporary table where the records get inserted as I do a cursor loop in a stored procedure. The problem is I am not sure if this at all the right way to accomplish what I am trying to do, but if it is, I can't seem to see any data in the temp table eventhough the proper number of records are created. Preferably I would appreciate it if you can tell me a better method for what I am trying to achieve - and I'll explain that now.

My basic query is to get a recordset of our products.
My products however belong to one of many possible categories (and is related by foreign key to a categories table).
Likewise, my products can belong to 1 of four seasons categories (also related to the Seasons table by a foreign key).
Sometimes we put an entire category of goods on sale by the Category they belong to and or by the Season they belong to.

So... As we loop through our products - we want to query the categories and season tables to see if there are any discounts which then need to be multiplied against our Products.Price field and returned to the recordset like so.

I think the logic will be clearer when you see the SQL below. I hope this makes sense.

Thanks in advance.

ALTER PROCEDURE sp_select_specials
            @parameter1 datatype = default value,
            @parameter2 datatype OUTPUT

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON

--Create a temporary table
              ID int IDENTITY,
      SKU varchar(50),
              Name varchar(50),
              Description varchar(5000),
      Thumb varchar(255),
      Price money,
      SeasonID int,
      CategoryID int,
      GenderID int,
      ConditionID int

--cursor variables
Declare @SKU varchar(50)
Declare @Name varchar(50)
Declare @Description varchar(5000)
Declare @Thumb varchar(255)
Declare @Price money
Declare @DiscountPrice money
Declare @SeasonID int
Declare @CategoryID int
Declare @GenderID int
Declare @ConditionID int
Declare @CategoryDiscount Decimal(18,3)
Declare @SeasonDiscount Decimal(18,3)
Declare @UseThisPrice money
Declare @FinalPrice money

Declare MyCursor CURSOR FOR

SELECT SKU, Name, Description, Thumb, Price, DiscountPrice, SeasonID, CategoryID, GenderID, ConditionID
FROM Products
WHERE FeaturedItem = 1

OPEN MyCursor

INTO @SKU, @Name, @Description, @Thumb, @Price, @DiscountPrice, @SeasonID, @CategoryID, @GenderID, @ConditionID


      --Determine what price to use. If no discount use use price else use discount price
      If @DiscountPrice > 0
      Set @UseThisPrice = @DiscountPrice
      Set @UseThisPrice = @Price

      -- Get Category Discount
      Select @CategoryDiscount = (Select CategoryDiscount From Categories Where CategoryID = @CategoryID)

      If @CategoryDiscount > 0
      Set @UseThisPrice = (@UseThisPrice - (@UseThisPrice * @CategoryDiscount))

      -- Get Season Discount
      Select @SeasonDiscount = (Select SeasonDiscount From Seasons Where SeasonID = @SeasonID)
      If @SeasonDiscount > 0
      Set @UseThisPrice = (@UseThisPrice - (@UseThisPrice * @SeasonDiscount))

INSERT INTO #TempItems (SKU, Name, Description, Thumb, Price, SeasonID, CategoryID, GenderID, ConditionID) Values (@SKU, @Name, @Description, @Thumb, @UseThisPrice, @SeasonID, @CategoryID, @GenderID, @ConditionID)

INTO @SKU, @Name, @Description, @Thumb, @Price, @DiscountPrice, @SeasonID, @CategoryID, @GenderID, @ConditionID

CLOSE MyCursor

Select * From #TempItems

-- Turn NOCOUNT back OFF

      /* SET NOCOUNT ON */
Question by:juliandormon
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 17

Accepted Solution

BillAn1 earned 200 total points
ID: 12262904
you can insert the whole recordset in one go with a single select statement using a case statement. However, you are inserting into a temp table, then exiting from the stored procedure. THis means that the temp table will be immediately dropped?
do you actually want to keep the results that you insert into the #temp table?

(SKU, Name, Description, Thumb, Price, SeasonID, CategoryID, GenderID, ConditionID)
     p.SKU, p.Name, p.Description, p.Thumb,
   (  (case when p.DiscountPrice > 0 then p.DiscountPrice else p.Price end) *
      ( 1 - ( case when CategoryDiscount  > 0 then CategoryDiscount else 0 end))
   ) *
   ( 1 - ( case when SeasonDiscount > 0 then SeasonDiscount else 0 end)),
   SeasonID,p.CategoryID, p.GenderID, p.ConditionID
Products p JOIN Categories C on p.CategoryID = c.CategoryID
JOIN Seasons s on p.SeasonID = s.seasonID

LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 100 total points
ID: 12263032
Ahhhh... if only the DiscountPrice was an amount to subtract from the regular price, rather than an independent field, or was null when not present (instead of zero).  This would be so much easier in those cases.

This can be done in a single SQL statement, but it is ugly in the extreme.  I think nested SQL woul be the best answer:

Select SKU, Name, Description, Thumb, Price - Coalesce( CategoryDiscount, 0) * Price) - (Coalesce(SeasonDiscount, 0) * (Price - (Coalesce( CategoryDiscount, 0) * Price))),      SeasonID, CategoryID, GenderID, ConditionID
      (SELECT SKU, Name, Description, Thumb,
           CASE WHEN DiscountPrice>0 Then DiscountPrice Else Price END As Price,
           SeasonID, CategoryID, GenderID, ConditionID,
           SeasonDiscount, CategoryDiscount
      FROM Products p
      LEFT JOIN Seasons s ON p.SeasonID = s.SeasonID
      LEFT JOIN Categories c ON p.CategoryID = c.CategoryID
      WHERE FeaturedItem = 1) SubTable
LVL 17

Expert Comment

ID: 12263268
there is no need to have nested queries. You get better perofmrance if you just do 3 multiplications.
If there is the case that sometimes there is no entry in the Seasons or Categort table etc you can use left outer join
also, if there is no need to remove any -ve numbers, you can remove the case altogether and just use the isnull function

     p.SKU, p.Name, p.Description, p.Thumb,
     (isnull(p.DiscountPrice,Price) *
     ( 1 - isnull(CategoryDiscount,0)) *
     ( 1 - isnull(SeasonDiscount,0)),
   SeasonID,p.CategoryID, p.GenderID, p.ConditionID
Products p LEFT OUTER JOIN Categories C on p.CategoryID = c.CategoryID
LEFT OUTER JOIN Seasons s on p.SeasonID = s.seasonID

Author Comment

ID: 12263517
thanks Guys,
With some fiddling, both your suggestions worked but BillAn1's answer seemed like it may be faster.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question