Solved

Create temp table while looping through cursor??

Posted on 2004-10-08
4
4,621 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now