DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('ardy', 97), ('ocky', 96)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0

--Select 2
SET @inputValue = 'Hardy, Rocky, ardy'
SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0

SET @inputValue = 'Hardy, Rocky, ardy'
SELECT * FROM @student WHERE CHARINDEX(', '+StudentName+',', ', '+@inputValue+',') > 0

SET @inputValue = 'Hardy,Rocky,ardy'
SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0

INSERT INTO @student 
VALUES ( 'Hardy', 100)
INSERT INTO @student 
VALUES ('Rocky', 98)
INSERT INTO @student 
VALUES ('Panky', 99) 

--Select 3
SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''
select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working

SELECT * FROM @student WHERE StudentName IN ('Hardy', 'Rocky') -- working OK

--Select 3
SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''
select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working

--Select 3
SET @inputValue1 = 'Hardy';
SET @inputValue2 = 'Rocky';
--select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue1, @inputValue2);