Passing string of parameters to stored procedure

I have a stored procedure that needs to accept a parameter @pnumber as a string of values like 1001,1002,1003...

My stored procedure looks like this:

create procedure dbo.getprojectdetails
    @pnumber nvarchar(255)
as
begin
    set nocount on

    declare @SQL varchar(1000);
    set @SQL  = 'SELECT pnumber, name, isprocessed FROM dbo.sample WHERE pnumber IN ('+''''+'p'+ @pnumber +''''+')'

    exec(@SQL)
end

My data:

create table sample
(
     pnumber varchar(500), 
     name varchar(500), 
     isprocessed int
)

insert into sample
values ('p1001', 'project1', 1),
       ('p1002', 'project2', 0),
       ('p1003', 'project3', 0),
       ('p1004', 'project4', 1),
       ('p1005', 'project5', 0),
       ('p1006', 'project6', 1)

select * from sample

The problem with this stored procedure is – when I input the parameter as (1001,1002) the query produced looks like this:

SELECT pnumber, name, isprocessed 
FROM dbo.sample 
WHERE pnumber IN ('p1001,1002')

My query should actually look as below:

SELECT pnumber, name, isprocessed 
FROM dbo.sample 
WHERE pnumber IN ('p1001', 'p1002')

Rextester link : https://rextester.com/USZRB48933

Is there a way to fix this?

Answer

No need for the Dynamic SQL

If 2016+, you can use string_split()

Example dbFiddle

Declare @S varchar(max)='p1001,p1002'   -- assumed missing p was a typo

select * 
 From  sample
 Where pnumber in ( select * from string_split(@S,',') )

Returns

pnumber name        isprocessed
p1001   project1    1
p1002   project2    0