Hi,

If I have a stored procedure that creates a table of 4 rows (recommended products for a given product). What's the best way to create a cached table of these 4 recommended products for EVERY product in a products table. (Cached table being 4 times bigger than product table)


Any Ideas welcome!

Hi alexstrong29 and welcome to DaniWeb :)

If the recommended products are also in the products table, what I would do is add four columns to your product table that represent the product ID's of each recommended product for the row. That way your database grows by a lot less than having a separate table that is four times larger than your current products table.

Hope this helps,
darkagn :)

Hi Darkagn, thanks for your reply.

I forgot to mention that I am trying to reduce hits on the product table which is the purpose for this cached table. Which will be updated over night!

I already have a select statement that you can pass one productID in but cant find an alternative to looping through this statement for each productID.

Do you think an alternative is possible?

Ah ok, sorry I misunderstood your intent. So in order to loop through each product, I would use a CURSOR. Basically a cursor provides a way to loop through objects in a table. Here's a simple example:

-- declare some variables to store the info for each product
DECLARE @ProdID int,
@Description varchar(30),
@Price smallmoney
-- create the cursor
DECLARE ProductCsr CURSOR FOR
  SELECT ProdID, Description, Price FROM Products
-- start the cursor and fetch the first record
OPEN ProductCsr
FETCH NEXT ProductCsr INTO @ProdID, @Description, @Price
-- Note that the order of values in the INTO part must match the order in the SELECT statement of the Cursor
-- loop through each record until we get to the end
WHILE @@FETCH_STATUS = 0
BEGIN
  -- here is where you do whatever you want to the data
  -- for example to update each record with a new price we could
  UPDATE Products SET Price = @Price + 1.50 WHERE ProdID = @ProdID AND Description = @Description
  -- fetch another record
  FETCH NEXT ProductCsr INTO @ProdID, @Description, @Price
END
-- cleanup after yourself
CLOSE ProductCsr
DEALLOCATE ProductCsr

This is a very simple example of how to loop through every record in a table. Cursors can be used for many different things, I think your situation would suit its purpose. Just remember to backup the database beforehand!

wow thats great, I will do some reading on cursors and wil try and implement. I have heard them mentioned before but have never put them in to practice so hopefully it will be a productive lesson in sql.

Thanks Again, I will let you know how I get on.

Alex

Hi everyone,

I hope that everyone is doing fine. I have a small problem. I have a table called Employee with a column called “Emp_name” if I need to print the first 5 characters of each name in the “Emp_Name” column, how do you suggest I should do it?

Hi everyone,

I hope that everyone is doing fine. I have a small problem. I have a table called Employee with a column called “Emp_name” if I need to print the first 5 characters of each name in the “Emp_Name” column, how do you suggest I should do it?

Hi zammari10 and welcome to DaniWeb :)

Your issue is a different one to this thread. I suggest you start a new topic so that more people will read your question and hopefully someone will have an answer for you.

Ah ok, sorry I misunderstood your intent. So in order to loop through each product, I would use a CURSOR. Basically a cursor provides a way to loop through objects in a table. Here's a simple example:

-- declare some variables to store the info for each product
DECLARE @ProdID int,
@Description varchar(30),
@Price smallmoney
-- create the cursor
DECLARE ProductCsr CURSOR FOR
  SELECT ProdID, Description, Price FROM Products
-- start the cursor and fetch the first record
OPEN ProductCsr
FETCH NEXT ProductCsr INTO @ProdID, @Description, @Price
-- Note that the order of values in the INTO part must match the order in the SELECT statement of the Cursor
-- loop through each record until we get to the end
WHILE @@FETCH_STATUS = 0
BEGIN
  -- here is where you do whatever you want to the data
  -- for example to update each record with a new price we could
  UPDATE Products SET Price = @Price + 1.50 WHERE ProdID = @ProdID AND Description = @Description
  -- fetch another record
  FETCH NEXT ProductCsr INTO @ProdID, @Description, @Price
END
-- cleanup after yourself
CLOSE ProductCsr
DEALLOCATE ProductCsr

This is a very simple example of how to loop through every record in a table. Cursors can be used for many different things, I think your situation would suit its purpose. Just remember to backup the database beforehand!

Hi darkagn,

Thank you for your wonderful codes, but i'm just wondering if I would like to do the above update every 1 year.
How do I go about doing that?

Thanks!