recursive stored procedure problem

Question

recursive stored procedure problem

SalFernandez

SSC Enthusiast

Points: 192
More actions
February 19, 2004 at 9:54 am

#107825

Dear All,
I have 2 SP's, one (let's call it sp_A) which returns a list of filesand another (let's call it sp_B) which recursively looks through amenu table to give me the location of the file. I call sp_B withinsp_A as I want to return the data in one table. I am having trouble ingetting the results back. Both SP's work independantly but when theyare put together I get an error. Any help will be greatlyappreciated!!
Thanks,
Jose
Code below...
sp_A
CREATE PROCEDURE spSearchTest
@Search nvarchar (50)
AS
-- NOTE: We're creating the temporary table and populating it before
we know the search results.
--1. Create a temporary table to hold the search results in.
CREATE TABLE #TempResults
( [ID] [int] IDENTITY (1, 1) NOT NULL ,
[FileID] [int] NOT NULL ,
[FileName] nvarchar (50),
[CategoryID] int,
[CategoryPath] nvarchar (250)
)
ON [PRIMARY]
--2. Run the search query and insert the results into the temporary
table.
INSERT INTO #TempResults (FileID, [FileName], CategoryID)
SELECT
MySupportFiles.FileID,
MySupportFiles.[FileName],
MySupportFileCategory.CategoryID
FROM
MySupportFiles
INNER JOIN MySupportFileCategory ON MySupportFiles.FileID =
MySupportFileCategory.FileID
INNER JOIN MySupportCategories ON MySupportFileCategory.CategoryID =
MySupportCategories.CategoryID
WHERE
--(MySupportCategories.CategoryDesc LIKE N'%' + @Search + '%') OR
--(MySupportCategories.CategoryName LIKE N'%' + @Search + '%') OR
(MySupportFiles.[FileName] LIKE N'%' + @Search + '%') OR
(MySupportFiles.LongDescription LIKE N'%' + @Search + '%')
OR
(MySupportFiles.ShortDesc LIKE N'%' + @Search + '%') OR
(MySupportFiles.Platform LIKE N'%' + @Search + '%')
--.3. Let's look at the results. See what the RowCount is and taken
action based on that.
DECLARE @RowCount int
DECLARE @Path nvarchar (250)
DECLARE @ID int
SET @RowCount = (SELECT COUNT(*) FROM #TempResults)
IF @RowCount IS NOT NULL
WHILE (@RowCount > 0)
BEGIN
SET @ID = (SELECT CategoryID FROM #TempResults WHERE [ID] =
@RowCount)
EXEC @Path = GetMySupportCategoryPath @ID
UPDATE #TempResults SET CategoryPath = @Path WHERE [ID] = @RowCount
-- Decrease the counter by 1
SET @RowCount = @RowCount - 1
END
-- 4. Return the data to the caller and delete the temporary table.
SELECT * FROM #TempResults
DROP TABLE #TempResults
GO
sp_B
CREATE PROCEDURE GetMySupportCategoryPath
@ID int
AS
DECLARE @ParentCategoryID int
DECLARE @CategoryName nvarchar(50)
-- 1. Create a temporary table. This code block is run just once.
IF @@NESTLEVEL = 1
BEGIN
CREATE TABLE #TempTable
(
[ID] [int] IDENTITY (1, 1) NOT NULL,
[CategoryName] [nvarchar] (50)
)
ON [PRIMARY]
END
-- 2. Select the CategoryName and put it in the temporary table.
SELECT
@ParentCategoryID = ParentCategoryID,
@CategoryName = CategoryName
FROM
MySupportCategories
WHERE
CategoryID = @ID
INSERT INTO #TempTable (CategoryName)
VALUES (@CategoryName)
-- 3. When the ParentCategoryID is -1 we have reached the top of the
hierarchy.
IF @ParentCategoryID = -1 AND @@NESTLEVEL < 32 -- max nesting level =
32
BEGIN
DECLARE @Path nvarchar (250)
DECLARE @RowCount int
SET @RowCount = (SELECT COUNT(*) FROM #TempTable)
SET @Path = ''
WHILE (@RowCount > 0)
BEGIN
SET @Path = @Path + (SELECT CategoryName FROM #TempTable WHERE [ID]
= @RowCount) + ' > '
-- Decrease the counter by 1
SET @RowCount = @RowCount - 1
END
-- Tidy up the string and return it
SET @Path = RTRIM(@Path)
SET @Path = SUBSTRING(@Path, 1, (LEN(@Path) - 1))
SELECT @Path
-- Delete the temporary table
DROP TABLE #TempTable
END
ELSE
EXEC GetMySupportCategoryPath @ParentCategoryID
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

This seems like a great place to use a function rather than a procedure. You would then be able to eliminate the cursor and use the function call directly in your initial insert statment.

Also, you don't state what the error is so it makes it very hard to figure out what the problem is!

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Mike Good SSCertifiable Points: 7415 More actions · Answer 2

Problem is this:

EXEC @Path = GetMySupportCategoryPath @ID

Your GetMySupportCategoryPath subroutine will return an integer return code, 0 in this case since you don't explicitly RETURN anything else.

The data that you see GetMySupportCategoryPath outputting when you run it by itself is being SELECTed and is returned as one (or more?) result sets. Couple ways to pass result set results from subroutine proc to calling proc:

A) create a temporary table in calling proc, and w/in subroutine proc existence of that table & populate it with INSERT stmts; or B) within your calling proc INSERT the results into a temporary table--this is probably cleaner approach.

Within spSearchTest you need to replace your current exec stmt with something like this:

create table #Path(Path nvarchar(250) null) --or not null?

insert #Path exec GetMySupportCategoryPath @ID

select @Path = Path from #Path --assuming above gets 1 row