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]

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?
jl**@totalise.c o.uk (Jose Perez) wrote in message news:<37******* *************** ****@posting.go ogle.com>...

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

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jose Perez (jl**@totalise. co.uk) writes:

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

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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp