Nested Stored Procedure (Singular!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested Stored Procedure (Singular!)

Author

Topic

mortoleeuk
Starting Member

5 Posts

Posted - 2008-09-20 : 14:16:29

Hello!

Wonder if anyone can spot whats wrong here, it errors on the line that calls the same stored procedure.

Here is the create procedure code...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[exigeSP_ListCategoriesUnderCategory]
	-- Add the parameters for the stored procedure here
	@i_parent_category_id bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @NodeList varchar(max)
DECLARE @category_id varchar(max)

SELECT @category_id = Min(i_category_id) FROM t_categories WHERE i_parent_category_id = @i_parent_category_id
IF @NodeList IS NOT NULL
	BEGIN
		SET @NodeList = @NodeList + ','
	END
ELSE
	BEGIN
		SET @NodeList = ''
	END

WHILE @category_id IS NOT NULL
	BEGIN
		SET @NodeList = @NodeList + @category_id
		SELECT @category_id = Min(i_category_id) FROM t_categories WHERE i_parent_category_id = @i_parent_category_id AND i_category_id > @category_id
	
		SET @NodeList = @NodeList + (exigeSP_ListCategoriesUnderCategory @category_id)
		IF @category_id IS NOT NULL
			BEGIN
				
				SET @NodeList = @NodeList + ','
			END
	END
SELECT @NodeList


END

Error...

Msg 102, Level 15, State 1, Procedure exigeSP_ListCategoriesUnderCategory, Line 34
Incorrect syntax near '@category_id'.

The reason for this SP is to create a list of category ID's underneath a certain category, and all of its sub categories.

Any ideas?

Thanks,
Lee

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-20 : 17:15:14

you can't do this:

SET @NodeList = @NodeList + (exigeSP_ListCategoriesUnderCategory @category_id)

you could do this though I think:

create table #temp(t nvarchar(max))
insert #temp exec exigeSP_ListCategoriesUnderCategory @category_id
select @NodeList = @NodeList + t from #temp

however there are probably better ways to accomplish this. have a look at CTE in books online for how you can do recursive queries in 2005.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 01:45:26

i think what you need is recursive CTE. something like below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[exigeSP_ListCategoriesUnderCategory]
	-- Add the parameters for the stored procedure here
	@i_parent_category_id bigint
AS
BEGIN
;With CTE(category_id,Level) AS
(
SELECT i_category_id,0 AS Level 
FROM t_categories 
WHERE i_parent_category_id =@i_parent_category_id
UNION ALL
SELECT  t.i_category_id,c.Level + 1 
FROM t_categories t
INNER JOIN CTE c
ON c.category_id=t.parent_category_id
)

SELECT * FROM CTE

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources