sql - SSMS Temp Table / Parameter Issue -

i have temp table 2 columns, each column parameter i've declared. i've done using sql.

declare   @sourcekey varchar(40) = '1109' ,@department key varchar(1500) = '14,55 

the table populated using following sql:

if object_id('tempdb..#department','u') not null     drop table #department   create table #department     (departmentkey int     ,baseterm varchar(5)) insert #department select value ,skt.key yyy.parselist(@department,',') join #sourcekeytable skt     on skt.key = skt.key 

if select * #department these results:

department key | sourcekey 14             |   1109 55             |   1109 

thats expect. join temp table main query so

join #department d         on table.rkey = d.departmentkey      

i need have temp table allow multi-select in visual studio report. however, department key equal 14 and 55 skewing results. need 1 value passed 14 or 55 not both. temp table neccessary multi-select.

any suggestions on how pass 1 value while still having set mentioned previously?
i'll best answer questions might not have explained question enough some.

i reckon need parse list temporary table or table variable , whatever needs done it.

it's difficult see code involve code below should illustrate idea sufficiently.

i create table variable. insert parsed list values , cycle through them printing values output

--create table variable declare @departments table(departmentorder int identity, rkey nvarchar(40) not null)  --create variables loop through table variable declare @departmentorder int declare @rkey nvarchar(40)   --populate table variable parsed list values insert @departments (rkey) select rkey yyy.parselist(@department,',')  --get first list entry select @departmentorder  = min(departmentorder ) @departments  --while we've not reached end  while @departmentorder  not null begin     --get department key entry     set @rkey  = (select rkey @departments departmentorder   = @departmentorder  )       --use values     print '@departmentorder  = '+convert(nvarchar(9),@departmentorder )     print '@rkey  = '''+@rkey  +''''      --get next list entry     set @departmentorder  = (select min(departmentorder ) @departments departmentorder  > @departmentorder ) end