Problem with Stored Procedure

[Jose Perez]

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],
MySupportFileCa tegory.Category ID
FROM
MySupportFiles
INNER JOIN MySupportFileCa tegory ON MySupportFiles. FileID =
MySupportFileCa tegory.FileID
INNER JOIN MySupportCatego ries ON MySupportFileCa tegory.Category ID =
MySupportCatego ries.CategoryID
WHERE
--(MySupportCateg ories.CategoryD esc LIKE N'%' + @Search + '%') OR
--(MySupportCateg ories.CategoryN ame LIKE N'%' + @Search + '%') OR
(MySupportFiles .[FileName] LIKE N'%' + @Search + '%') OR
(MySupportFiles .LongDescriptio n 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 = GetMySupportCat egoryPath @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 GetMySupportCat egoryPath
@ID int
AS

DECLARE @ParentCategory ID 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
@ParentCategory ID = ParentCategoryI D,
@CategoryName = CategoryName
FROM
MySupportCatego ries
WHERE
CategoryID = @ID

INSERT INTO #TempTable (CategoryName)
VALUES (@CategoryName)

-- 3. When the ParentCategoryI D is -1 we have reached the top of the
hierarchy.
IF @ParentCategory ID = -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 GetMySupportCat egoryPath @ParentCategory ID
GO

[swtwllm]

Re: Problem with Stored Procedure

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.c o.uk (Jose Perez) wrote in message news:<3724a9d9. 0402190236.2b12 58e3@posting.go ogle.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],
> MySupportFileCa tegory.Category ID
> FROM
> MySupportFiles
> INNER JOIN MySupportFileCa tegory ON MySupportFiles. FileID =
> MySupportFileCa tegory.FileID
> INNER JOIN MySupportCatego ries ON MySupportFileCa tegory.Category ID =
> MySupportCatego ries.CategoryID
> WHERE
> --(MySupportCateg ories.CategoryD esc LIKE N'%' + @Search + '%') OR
> --(MySupportCateg ories.CategoryN ame LIKE N'%' + @Search + '%') OR
> (MySupportFiles .[FileName] LIKE N'%' + @Search + '%') OR
> (MySupportFiles .LongDescriptio n 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 = GetMySupportCat egoryPath @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 GetMySupportCat egoryPath
> @ID int
> AS
>
> DECLARE @ParentCategory ID 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
> @ParentCategory ID = ParentCategoryI D,
> @CategoryName = CategoryName
> FROM
> MySupportCatego ries
> WHERE
> CategoryID = @ID
>
> INSERT INTO #TempTable (CategoryName)
> VALUES (@CategoryName)
>
> -- 3. When the ParentCategoryI D is -1 we have reached the top of the
> hierarchy.
> IF @ParentCategory ID = -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 GetMySupportCat egoryPath @ParentCategory ID
> GO[/color]

[Erland Sommarskog]

Re: Problem with Stored Procedure

[posted and mailed, please reply in news]

Jose Perez (jlpv@totalise. co.uk) writes:[color=blue]
> 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!!
>...
> DECLARE @Path nvarchar (250)
>...
>
> EXEC @Path = GetMySupportCat egoryPath @ID
>...
> CREATE PROCEDURE GetMySupportCat egoryPath
> @ID int
> AS[/color]

The return value of a stored procedure is always an integer. (And should
in my opinion, only be used to indicate success/failure, with 0 indicating
success, and everything else failure.) The above could have worked ir
GetMySupportCat egoryPath had been a scalar user-defined function.

For a stored procudure, you need to use an output parameter:

CREATE PROCEDURE outpar_sp @outpar int OUTPUT
SELECT @outpar = 4711
do
DECLARE @outpar int
EXEC outpar_sp @outpar OUTPUT
SELECT @outpar

Note that you need to specify OUTPUT both in declaration and in EXEC
statement!

But there are more problems. The inner procedure has:

IF @@NESTLEVEL = 1
BEGIN
CREATE TABLE #TempTable
(
[ID] [int] IDENTITY (1, 1) NOT NULL,
[CategoryName] [nvarchar] (50)
)
ON [PRIMARY]
END

But since you call the inner procedure from the outer, @@nestlevel is 2,
and the table never gets created.




--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp