i have this procedure that gets a list of a IDs in a self jon it gets detected as action procedure but of course i am expecting the resultset is there a way around that ?

here goes

ALTER PROCEDURE [dbo].[GetChildren] @unitID int
AS
BEGIN
set nocount on
create table ##temp
(
    unitID int,
    processed bit
)
insert into ##temp (unitID,processed)
select childunit_ID, 0 from unitstree where parentUnit_ID = @unitID
declare @found  bit
set @found=1
declare @foundUnitID int
while @found=1
begin
    declare children cursor for
    select unitID from ##temp where processed=0
    open children 
    fetch next from children into @foundUnitID
    while @@fetch_status=0
    begin
        insert into ##temp (unitID,processed)
        select childunit_ID, 0 from unitstree where parentUnit_ID = @foundUnitID
        update ##temp set processed=1 where unitID=@foundUnitID
        fetch next from children into @foundUnitID
    end
    close children
    deallocate children
    if exists (select unitID from ##temp where processed=0)
        set @found=1
    else
    set @found=0
end
select unitID  from ##temp
drop table ##temp
end

i also tried to execute it under another procedure but still the same

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3593

Please check the above thread, it was discussing the same issue.

Thanks

thank you that worked