SQL Server T-SQL IN clause with parameter!!

It is common scenario that, like any other SQL query with parameter, we will expect IN clause also work same, but it not straight forward.
For Example :
                       if you would like fetch the details of only Subcategory "Chains" and "Forks", your query would look like this.

SELECT *
FROM ADVENTUREWORKSDW2014.[DBO].[DIMPRODUCTSUBCATEGORY]
WHERE ENGLISHPRODUCTSUBCATEGORYNAME IN (
  'CHAINS'
  ,'FORKS'
  )

Well, this works great!
What if this code is part of stored procedure and number of subcategory is unkown.
We can simply write  stored procedure like,

CREATE PROCEDURE GET_SUBCAT_DETAILS @SUB_CAT VARCHAR(250) = 'CHAINS,FORKS'
AS
BEGIN
 SELECT *
 FROM ADVENTUREWORKSDW2014.[DBO].[DIMPRODUCTSUBCATEGORY]
 WHERE ENGLISHPRODUCTSUBCATEGORYNAME IN (@SUB_CAT)
END

EXEC GET_SUBCAT_DETAILS 

Output : zero rows :(

Yes, we can not just use parameter directly like this, Internally it will consider 2 values as one,.

Work around is to use dynamic query like this,

CREATE PROCEDURE GET_SUBCAT_DETAILS @SUB_CAT VARCHAR(250) = 'CHAINS,FORKS'
AS
BEGIN
 DECLARE @SQL NVARCHAR(1000) = 'SELECT * FROM ADVENTUREWORKSDW2014.[DBO].[DIMPRODUCTSUBCATEGORY] WHERE ENGLISHPRODUCTSUBCATEGORYNAME IN (''' + REPLACE(@SUB_CAT, ',', '''' + ',' + '''') + ''')'

 EXEC SP_EXECUTESQL @SQL
END