Help me get rid of my cursor. Please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me get rid of my cursor. Please?

Author

Topic

pdupree72
Starting Member

3 Posts

Posted - 2005-03-28 : 14:09:58

I am dying here. I know using a cursor is bad news, and I want to get away from it. But I am a newby, and I need your help figuring this one out.

I currently have a cursor in a stored proceedure that hits a table and pulls back each one of the values in a specific column.

Below is the code I am currently using. Can anyone out there help a guy out please?

DECLARE Product_Cursor CURSOR FOR
SELECT idProduct from products
WHERE idProduct!=''
ORDER BY idProduct

DECLARE @ProductIDFetched INT

OPEN Product_Cursor

FETCH NEXT FROM Product_Cursor INTO @ProductIDFetched

WHILE (@@FETCH_STATUS = 0)
BEGIN
<SOME OTHER CODE THAT IS NOT NEEDED IN SNIPPET>
FETCH NEXT FROM Product_Cursor INTO @ProductIDFetched
END
END
CLOSE Product_Cursor
DEALLOCATE Product_Cursor
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-28 : 14:14:38

But what are you doing inside the cursor? That's what we need to know in order to help you out. We need to see what you are doing with @ProductIDFetched.

Tara

pdupree72
Starting Member

3 Posts

Posted - 2005-03-28 : 14:33:30

Here is the whole store proceedure:

--Set up the cursor Product_Cursor which will
--get each of the product ids from the products table
DECLARE Product_Cursor CURSOR FOR

--Select statement for Product_Cursor
--Gets all of the Product ID's that are not NULL
SELECT idProduct from products
WHERE idProduct!=''
ORDER BY idProduct

--ProductIDFetched is the Product ID gathered from Product_Cursor
DECLARE @ProductIDFetched INT

--Open the Cursor to get the Product ID's
OPEN Product_Cursor

--Fetch the First Product ID and store it in @ProductIDFetched
FETCH NEXT FROM Product_Cursor INTO @ProductIDFetched

--Loop that continues until ProductFetcher comes back empty
WHILE (@@FETCH_STATUS = 0)

BEGIN
SELECT TOP 15

--The BaseProductID IE: Product purchased
@ProductIDFetched as BaseProductID,
--The RelatedProductID IE: People also purchased
--this product too
p.idProduct as RelatedProductID,

--This is the call to RelatedProductScore Function
--The first var is idScoreProduct
--The second var is idBaseProduct
--As defined in RelatedProductScore Function
dbo.RelatedProductScore(p.idProduct, @ProductIDFetched) as Score

FROM
products p
left outer join tb_StockUpdate su on p.sku = su.itemCode
join tb_ProductAttributes pa on pa.idProduct = p.idProduct

--Order by Score
ORDER BY
Score desc

--Get the next record from the cursor
FETCH NEXT FROM Product_Cursor INTO @ProductIDFetched

END
--Close the Product_Cursor
CLOSE Product_Cursor
--Deallocate the Product_Cursor
DEALLOCATE Product_Cursor
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-28 : 14:58:01

I think what you want is this:
http://www.sqlteam.com/item.asp?ItemID=3856

If not, then it might help if you provide DDL for your tables involved in this query, sample data for these tables, and the expected result set using this sample data. A few rows per table would be all that is necessary. DDL is CREATE TABLE statements. Sample data should be provided in INSERT INTO statements. This is so that we can copy the code onto our machines and run it there.

Tara

pdupree72
Starting Member

3 Posts

Posted - 2005-03-28 : 15:52:47

Thanks for the information, but I do not think it answers my question completly.

I am using the cursor to grab a productID as a variable. I then pass the productID to a function that finds other products purchased in conjunction with the original productID. These two values (and a another value which ranks the 2 products based on cost, selling price, and dealer returned from the function) are then inserted into a cache table.

I am using a cursor because I need to go through the productIDs one by one and pass them to the function.

Does this clear things up at all?

Thanks for your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-28 : 17:16:35

You'll also need to give us the script of the function that's called from within your cursor and DDL for any tables the function uses.

Be One with the Optimizer
TG

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-28 : 18:08:42

Here's one way, but it kind of sucks; if you're willing to get rid of the function there's probably a better way:


SELECT
p.idproduct as BaseProductID,
p2.idProduct as RelatedProductID,
dbo.RelatedProductScore(p.idProduct, p2.idproduct) as Score
FROM products p
CROSS JOIN products p2
WHERE p2.idProduct IN
	(SELECT TOP 15 
		p33.idProduct
	FROM products p3
	ORDER BY dbo.RelatedProductScore(p.idProduct, p3.idproduct) DESC)

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources