Stored procedure IN Clause variable list

[crystalReporterNew]

Hi,
I need a way to write the following query: The problem is that I pass a variable sized Comma separated list of Strings that I need to include in the IN clause of the query.The @RepList variable will look like '5674','2345','3455'.... and so on.
However if I pass that as @RepList as a string it does not recognize the delimited commas and treats the whole list as a single string and returns wrong results(non actually).How can I do this in the most efficient manner?

create procedure spGetAccounts(@AccNum varchar(10),
@RepList varchar(255))
as
begin

select a.AcctNum, a.AcctName, a.RepId
from Account a
where a.AcctNum LIKE @AccNum
AND a.RepId IN (@RepList)

end

 

[micha123]

You can do one of the following:

1. Separate the strings and insert each value to a temporary table and then :

a.RepId in (select ... from #tmp)

2. make a synamic SQL:

EXEC ('select a.AcctNum, a.AcctName, a.RepId
   from Account a
   where a.AcctNum LIKE @AccNum
   AND a.RepId IN ( + @RepList + ')')]/CODE]


 

[COLOR=blue][b][i][u]"A long life is when each day is full and every hour wide!"[/u][/i][/b][/color]
                  (A Jewish Mentor)
M.   [hourglass]