WHERE int IN varcharlist ERROR 1

[cfaulkner]

I have spent about 10 hours on this issue so far trying everything that I can think of to work around this so any help would be greatly appreciated. I just feel like there should be simple solution here, I just may not be seeing the forest through the trees.

I am trying to find all employees unders a manager in an employees table. Manager being generic for anyone with direct reports; so a VP, director, or supervisor would have a mgrid (manager ID). The issue is recursing down to the lowest level employee under a VP, manager, or supervisor. So for a VP there would be a managers, supervisors, and employees. The employees table has an emplid (employee ID) and a mgrid (manager ID).

CODE:
declare @rowcount int -- exit when no more rows are
-- returned

declare @emplids varchar(8000)

set @emplids = '255,' -- initial manager ID (could be
-- VP, etc).

-- Recurs through adding emplid's for each level.
while @rowcount > 0 or @rowcount is null
begin
print 'begin: '+@emplids
select @emplids = @emplids + ltrim(str(emplid)) + ','
from dbo.temployees
where mgrid in (left(@emplids,(len(@emplids)-1)))
set @rowcount = @@rowcount
print 'end: '+@emplids
print @emplids
end

RESULT / ERROR MESSAGE:
begin: 255,
end: 255,67,79,124,299,418,460,685,
255,67,79,124,299,418,460,685,
begin: 255,67,79,124,299,418,460,685,
Server: Msg 245, Level 16, State 1, Line 8
Syntax error converting the varchar value '255,67,79,124,299,418,460,685' to a column of data type int.


The problem is that mgrid is an int and so SQL is trying to convert the in list to an int. This works on the first pass through because SQL can convert the 255 to an int of 255. It can not convert the longer in list though. Any thoughts? Work arounds?

Thanks!
Craig

 

[SQLBill]

One thing, why are the numbers duplicated in the "end output"?

end: 255,67,79,124,299,418,460,685,
255,67,79,124,299,418,460,685,

Another, you can't turn a string into an INT value. Convert the INT value to a VARCHAR and see if it works for you.

-SQLBill


Posting advice: FAQ481-4875

 

[cfaulkner]

Oh yes, the duplication. I forgot about that for the moment. I tried converting the where 'int' into a varchar but received an error.

BTW, this works but is it limited to the number of selects and VERY ugly; to me anyway.

select emplid
from dbo.temployees
where mgrid = 255
union
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid = 255)
union
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid = 255))
union
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid = 255)))
union
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid in (
select emplid
from dbo.temployees
where mgrid = 255))))

 

[vongrunt]

Nice ASCII art

This is shorter version:

select distinct A.emplid
from temployees A
left outer join temployees B on B.emplid=A.mgrid
left outer join temployees C on C.emplid=B.mgrid
left outer join temployees D on D.emplid=C.mgrid
left outer join temployees E on E.emplid=D.mgrid
where 255 in (B.emplid, C.emplid, D.emplid, E.emplid)

For really infinite depth things are a bit more complex (nested sets/intervals), but there is a great chance you don't need more than 5-6 anyway.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mercwrought]

Ok here is my idea there is no limit to the number of bosses or employees I came up with this for a merge list I use in a tree control

See if it helps

create table #emp ( emp int)
declare @boss int
set @boss = 255 -- start manager

insert into #emp select @boss -- start manager

while (select count(*) from temployees 
	where mgrid in (select emp from #emp) 
	and  emplid Not in (select emp from #emp))>0
BEGIN
	insert into #emp 
		select emplid from temployees 
		where mgrid in (select emp from #emp) 
		and  emplid Not in (select emp from #emp)
end


select distinct emp from #emp where emp <> @boss

 

[cfaulkner]

Both excellent suggestions. Thank you! Thank you! I'll play with them both and will likely include this in a function table. Sometimes I just like to complicate my life (i.e. my initial effort).

Craig

 

[vongrunt]

Wow, that's simple and elegant.

Side note: this can be made shorter:

...
insert into #emp select @boss

while @@rowcount > 0
begin
    insert into #emp 
        select emplid from temployees 
        where mgrid in (select emp from #emp) 
        and  emplid Not in (select emp from #emp)
end...

... and if org. structure is vanilla tree (no cyclical references, all employees have at most one manager) final DISTINCT may not be necessary.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cfaulkner]

vongrunt

And it works! Popped it into a function table with a few minor changes and I'm good to go. That made my day.

 

[mercwrought]

Thx for the star, and vongrunt thx for the praise. Like I said I wrote something a little more complicated for a sp that I have that returns the input for a tree control that displays accounts that have been merged into each.