asked on

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

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.

ASKER CERTIFIED SOLUTION

inford

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

perkley

ASKER

OK, I am having trouble with the execute part, I try putting the SET @idList = " EXECUTE, but I just get an error, so I remove the quotes and I get this error:

---------- ERROR MESSAGE ----------

Incorrect syntax near the keyword 'EXECUTE'.
The identifier that starts with '

DECLARE @sqlStatement varchar(4000)
SET @sqlStatement = 'select @ForumNewPostCount = count(id) from forum wh' is too long. Maximum length is 128.
Unclosed quotation mark after the character string '

DECLARE @sqlStatement varchar(4000)
SET @sqlStatement = 'select @ForumNewPostCount = count(id) from forum whe'.
Incorrect syntax near '

DECLARE @sqlStatement varchar(4000)
SET @sqlStatement = 'select count(id) from forum wh'.

perkley

ASKER

Okay, I figured out the Execute, I didn't realize you were doing two single quotes. Anyway, it is still not working like I wanted. For some reason if I pass in a parent and even though I can print out all the children, when I try to add them to each other, it doesn't do it and only returns one number, the one I passed in.

SET @idList = @idList + ',' + CAST(@IndName AS VARCHAR(100))

I am playing around with it and your help as been most beneficial. I think I am getting really close and any more suggestions would be great.

perkley

ASKER

I have solved the @idList issue, I had to use the output on my nested execute, so my procedure looks as follows now.

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

      SET @IndName = (SELECT ID FROM dbo.forum_industries WHERE ID = @Root)

IF @idList <> ''
      SET @idList = @idList + ',' + @IndName
      ELSE
      SET @idList = @IndName

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

      WHILE @IndID IS NOT NULL
      BEGIN
       EXEC dbo.ShowHierarchy @IndID, @idList output
SET @IndID = (SELECT MIN(ID) FROM dbo.forum_industries WHERE ParentID = @Root AND ID > @IndID)

END
END
[/code]

However, in my trigger I am not sure how to get the value returned from this line

EXEC(@SQLStatement)

I want to update that into my forum_industries table, so I am using the following:

UPDATE forum_Industries SET ThreadCount = @PostCount
WHERE ID = @IndID

The @PostCount is not defined, but I need to know how to get that from the executed query, something like this, only that works.

DECLARE PostCount int
SET @vPostCount = EXEC(@SQLStatement)

perkley

ASKER

Finally, I have done it. Well, I just put my update in with the @sqlStatement
SET @sqlStatement = 'UPDATE forum_industries SET ThreadCount = (select count(id) from forum where forum_industriesID IN (' + @idList + ')) WHERE ID = '+CAST(@indID AS VARCHAR(8))

I did do some research and it sounds like I could also use sp_executesql and define the variable to get an output, but this seemed to be the simplest way.

perkley

ASKER

My problem was solved