Run a stored procedure for every product in a products table

[alexstrong29]

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!

[darkagn]

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 :)

[alexstrong29]

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?

[darkagn]

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!

[alexstrong29]

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

[zammari10]

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?

[zammari10]

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?

[darkagn]

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.

[germaine.tay]

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!