March 12, 2007 at 9:06 am
Server: Msg 16915, Level 16, State 1, Procedure ProductCategoryTree, Line 16
A cursor with the name 'rs' already exists.
PRODUCTCATEGORIES
CategoryId | CategoryName | ParentId
------------|------------------|----------
01 | Men's Clothing | 00
02 | Women's Clothing | 00
03 | Men's Shirts | 01
04 | Men's Pants | 01
05 | Women's Shirts | 02
06 | Women's Pants | 02
07 | Blouses | 05
08 | Dresses | 02
09 | Skirts | 02
PRODUCTS
Sku | Description | Vendor | CategoryId
----|-----------------|--------|------------
01A | Blue Skirt | Amanda | 04
02C | Men's Jeans L | Charla | 04
03B | White Blouse S | Bonnie | 07
04B | White Blouse XS | Bonnie | 07
05C | Print T-shirt | Charla | 03
06A | Men's Slacks M | Amanda | 04
07B | Daisy Pattern | Bonnie | 08
____________________________________________________________________________
CREATE FUNCTION ProductCategoryTree (@CategoryId int)
RETURNS varchar(200)
AS
BEGIN
DECLARE @return varchar(200)
DECLARE @category int
DECLARE @categoryname varchar(200)
SET @category = @CategoryID
WHILE @category <> 0
BEGIN
DECLARE rs cursor FOR
SELECT CategoryName FROM ProductCategories
WHERE CategoryID = @category
DECLARE rsParent cursor FOR
SELECT ParentCategory FROM ProductCategories
WHERE CategoryID = @category
OPEN rs
FETCH rs INTO @categoryname
CLOSE rs
OPEN rsParent
FETCH rsParent INTO @category
CLOSE rsParent
SET @return = @categoryname + ' >> ' + @return
END
RETURN @return
END
SELECT dbo.ProductCategoryTree(CategoryId), Description
FROM Products WHERE Vendor = 'Bonnie'
ORDER BY dbo.CategoryTree(CategoryId)
Category Tree | Description
-----------------------------------------------|-----------------
Women's Clothing >> Dresses | Daisy Pattern
Women's Clothing >> Women's Shirts >> Blouses | White Blouse S
Women's Clothing >> Women's Shirts >> Blouses | White Blouse XS
March 12, 2007 at 12:09 pm
You have to deallocate the cursor after you close the cursor.
Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft® SQL Server™.
March 12, 2007 at 9:05 pm
It worked... thanks!!!!
ALTER FUNCTION dbo.ProductCategoryTree
(
@CategoryID int
)
RETURNS varchar(200)
AS
BEGIN
DECLARE @return varchar(200)
DECLARE @category int
DECLARE @categoryname varchar(200)
SET @return = ''
SET @category = @CategoryID
WHILE @category <> 0
BEGIN
DECLARE rs cursor FOR
SELECT CategoryName FROM ProductCategories
WHERE CategoryID = @category
OPEN rs
FETCH rs INTO @categoryname
CLOSE rs
DECLARE rsParent cursor FOR
SELECT ParentCategory FROM ProductCategories
WHERE CategoryID = @category
OPEN rsParent
FETCH rsParent INTO @category
CLOSE rsParent
IF @return <> '' SET @return = ' >> ' + @return
SET @return = @categoryname + @return
DEALLOCATE rs
DEALLOCATE rsParent
END
RETURN @return
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply