We help IT Professionals succeed at work.
Get Started

SQL 2005 Nested Stored Procedure, how to return a value to a trigger

perkley
perkley asked
on
623 Views
Last Modified: 2012-05-10
I am trying to write a stored procedure that I can call from a trigger that will return a comma separated ID list of the parent and it's children.  This is what I have for the stored procedure:

CREATE PROCEDURE dbo.ShowHierarchy
(
	@Root int,
    @idList varchar(100) OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @IndID int, @IndName varchar(30)

	SET @IndName = (SELECT ID FROM dbo.forum_industries WHERE ID = @Root)
	PRINT REPLICATE('-', @@NESTLEVEL * 4) + @IndName
    
    SET @idList = @idList + ',' + CAST(@IndName AS VARCHAR(100))

	SET @IndID = (SELECT MIN(ID) FROM dbo.forum_industries WHERE ParentID = @Root)

	WHILE @IndID IS NOT NULL
	BEGIN

        EXEC dbo.ShowHierarchy @IndID, @idList--, @finalIdList
		SET @IndID = (SELECT MIN(ID) FROM dbo.forum_industries WHERE ParentID = @Root AND ID > @IndID)
           
    END
	
END

Open in new window


When I call this, I do get in the message the right ID's displayed.  By the way, the @IndName is actually the ID and I will name it better once I get this working.

In my Trigger I am not sure how to call it and get the value back.  My trigger code

BEGIN
    DECLARE @IndID int
    DECLARE @idList varchar

	-- Find out whether we check the inserted or deleted table, depending on whether a record is getting deleted or inserted or updated.
	if (select count(*) from inserted) <> 0 and (select count(*) from deleted) = 0 --insert
	begin
		SELECT @IndID = forum_industriesID FROM inserted
	end
	if (select count(*) from inserted) <> 0 and (select count(*) from deleted) <> 0 --update
	begin
		SELECT @IndID = forum_industriesID FROM inserted
	end
	if (select count(*) from inserted) = 0 and (select count(*) from deleted) <> 0 --delete
	begin
		SELECT @IndID = forum_industriesID FROM deleted
	end
    
        
    SET @idList = 0
    EXECUTE ShowHierarchy @IndID, @idList
  -- Query code to store the IDList or select based on the list
END

Open in new window


So, my hope is that my trigger will call this ShowHIerarchy and pass in the root, and return a value like 5,6,7,10,50,100  and I can use that in a query like: select count(id) from table where forum_industriesID IN (@idList)

The code I have so far returns nothing, so I don't know how to get the values back out.

I am fairly new to writing stored procedures and triggers and so any suggestions would be most helpful.
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE