TSQL query : passing parameter to sp for in clause

The easy way to do this would be to use an SQL SPLIT function in your statement. This function was added with SQL Server 2016, previous versions would require a User Defined Function to have been installed. If this is the case there are plenty available on sites such as Sql Server Central
Good Read: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[^]

This statement is using the 2016+ built in function:

DECLARE @ID NVARCHAR(3) = '001'
DECLARE @LocationIDs NVARCHAR(100) = '1,2,3'

SELECT Name
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT(@LocationIDs, ','))

The second option is a little more complicated, requiring SQL User Defined Types as well as different syntax within the ADO call. The advantage is you can pass in more complex types such as DataTables, DataReaders, or iEnumerables

SQL Portion

CREATE TYPE dbo.UserTableType_INTs AS TABLE( IntValue INT NOT NULL)
GO
CREATE PROCEDURE DBO.usp_MyProcedure (
  @ID          NVARCHAR(100),
  @LocationIDs dbo.UserTableType_INTs READONLY
) AS
BEGIN
  SELECT Name
  FROM   Table
  WHERE  Id = @ID
  AND    LocationID IN @LocationIDs
END
GO

And calling it via ADO in C#

// Sample datatable, I generally don't do this and my syntax may be off
DataTable LocationIDs = new DataTable();
LocationIDs.Clear();
LocationIDs.Columns.Add("IntValue");
LocationIDs.Rows.Add(1);
LocationIDs.Rows.Add(2);
LocationIDs.Rows.Add(3);

SqlCommand cmd = new SqlCommand(sqlInsert, connection);
cmd.CommandType = CommandType.StoredProcedure;

// standard input parameter shortcut
cmd.Parameters.AddWithValue("@ID", "001"); 

// different parameter addition when using an SQL User Type
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@LocationIDs", LocationIDs); 
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.UserTableType_INTs";

SELECT Name 
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT (@LocationIDs, ','))

You canot use @var in IN statement unless its dynamic SQL.

Declare @var NVARCHAR(100) = '1,2,3';
declare @sql varchar(300) = 'SELECT * FROM TBL WHERE ID IN (' + @var + ');';
exec(@sql)

Orelse you can use split function of string to split @var and use directly.

This is awesome answer.

SELECT value FROM STRING_SPLIT(@LocationIDs, ',')

I have been searching a lot and this was the best solution I got. Thanks a lot