Solved

Create temp table while looping through cursor??

Posted on 2004-10-08
4
4,651 Views
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
      )
*/
AS

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


--Create a temporary table
CREATE TABLE #TempItems
(
              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
ORDER BY Name ASC

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @SKU, @Name, @Description, @Thumb, @Price, @DiscountPrice, @SeasonID, @CategoryID, @GenderID, @ConditionID

WHILE @@FETCH_STATUS = 0
BEGIN

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

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

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

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


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


FETCH NEXT FROM MyCursor
INTO @SKU, @Name, @Description, @Thumb, @Price, @DiscountPrice, @SeasonID, @CategoryID, @GenderID, @ConditionID
END


CLOSE MyCursor
DEALLOCATE MyCursor

Select * From #TempItems

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF  

      /* SET NOCOUNT ON */
      RETURN
0
Comment
Question by:juliandormon
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
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?

INSERT INTO #TempItems
(SKU, Name, Description, Thumb, Price, SeasonID, CategoryID, GenderID, ConditionID)
SELECT
     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
FROM
Products p JOIN Categories C on p.CategoryID = c.CategoryID
JOIN Seasons s on p.SeasonID = s.seasonID

0
 
LVL 32

Assisted Solution

by:bhess1
bhess1 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
FROM
      (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
ORDER BY Name Asc
0
 
LVL 17

Expert Comment

by:BillAn1
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

SELECT
     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
FROM
Products p LEFT OUTER JOIN Categories C on p.CategoryID = c.CategoryID
LEFT OUTER JOIN Seasons s on p.SeasonID = s.seasonID
0
 

Author Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

776 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