Dear All,
I have 2 SP's, one (let's call it sp_A) which returns a list of files
and another (let's call it sp_B) which recursively looks through a
menu table to give me the location of the file. I call sp_B within
sp_A as I want to return the data in one table. I am having trouble in
getting the results back. Both SP's work independantly but when they
are put together I get an error. Any help will be greatly
appreciated!!
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
