How to pass string parameter with `IN` operator in stored procedure SQL Server 2008

Question

I have a stored procedure when I execute it I got error

Conversion failed when converting the varchar value '+@dptId+' to data type int

I am getting DepartmentId as a string like (1,3,5,77) and am passing this to my stored procedure.

SQL FIDDLE

create table dummy (id int,name varchar(100),DateJoining Datetime, departmentIt int)

insert into dummy values (1,'John','2012-06-01 09:55:57.257',1);
insert into dummy  values(2,'Amit','2013-06-01 09:55:57.257',2);
insert into dummy  values(3,'Naval','2012-05-01 09:55:57.257',3);
insert into dummy  values(4,'Pamela','2012-06-01 09:55:57.257',4);
insert into dummy  values(5,'Andrea','2012-09-01 09:55:57.257',3);
insert into dummy  values(6,'Vicky','2012-04-01 09:55:57.257',4);
insert into dummy  values(7,'Billa','2012-02-01 09:55:57.257',4);
insert into dummy  values(8,'Reza','2012-04-01 09:55:57.257',3);
insert into dummy  values (9,'Jacob','2011-05-01 09:55:57.257',5);

Query I tried:

declare @startdate1 varchar(100) ='20120201'
declare @enddate1 varchar(100)='20130601'
declare @dptId varchar(100)='3,4'

select * 
from dummy
where DateJoining >= @startdate1 and DateJoining < @enddate1 
  and departmentIt IN (@dptId);

Answer 1

Try using sp_executesql as the answer. Not the most efficient but it works

ALTER PROCEDURE [dbo].[uspTestReportData_GetBySerial] 
    @SerialNumbers nvarchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    declare @sql nvarchar(200)

    set @sql = 'SELECT * from MyTable WHERE  Serial_Number in (' + @SerialNumbers + ')'
    execute sp_executesql @sql                    

END

Answer 2

Simply, you can do the following SELECT:

SELECT M.REG_NO, T.TYPE_ID 
    FROM MAIN AS M 
        INNER JOIN CLASSIFICATION AS C 
            ON M.REG_NO = C.REG_NO
        INNER JOIN TYPE AS T 
            ON T.TYPE_ID = C.TYPE_ID
    WHERE (','+@Types+',') LIKE '%,' +T.TYPE_ID+ ',%'