Dude, you need to give more information.
1. What is the error thrown?
2. What is the structure of the tables used in the sproc?
jlpv@totalise.co.uk (Jose Perez) wrote in message news:<3724a9d9.0402190236.2b1258e3@posting.google. com>...[color=blue]
> 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[/color]
