how to write a stored procedure when a varchar variable is passed?

[anitalulu]

I want to write the following Stored Procedure:
---------------------------------------------------------------------------
CREATE procedure sp_GetFirstNode (@id int, @permission_id varchar(1000))
as
begin

select top 1 *
into #temp
from InfoTypeMaster where
parent = @id
order by seq

while @@rowcount > 0
begin
insert into #temp
select top 1 *
from InfoTypeMaster
where Parent in (select InfoTypeID from #temp)
and InfoTypeID not in (select InfoTypeID from #temp)
and InfoTypeID in (@permission_id)
end

select top 1 * from #temp
drop table #temp

end
-----------------------------------------------------------------------

It is a tree hierrarchy. I want to find the first node of the given id.

the expected parameters are

exec sp_GetFirstNode 1, '0,10,11,12,13,14,15,16,17,18,19,1,20,21,22,23,24,25,26,27,28,29,2,30,31,32,33,34,35,36,37,38,39,3,40,41,42,43,44,45,46,47,48,49,4,50,51,52,53,54,55,56,57,58,59,5,60,61,62,63,64,65,66,67,6,7,8,9'


@id: the root of the sub-tree
@permission_id: those IDs have the permission to read

The problem is @permission_id which is varchar. I cannot pass it into the subquery. Any suggestion?

 

[JayKusch]

substitute your current While statment w/ this one. put the declare at the top of the SP.

declare @sql_command varchar(4000)

'while @@rowcount > 0
begin
insert into #temp
select top 1 *
from InfoTypeMaster
where Parent in (select InfoTypeID from #temp)
and InfoTypeID not in (select InfoTypeID from #temp)
and InfoTypeID in (' + @permission_id + ')
end'

exec (@sql_Command)



Thanks

J. Kusch