SET keyword in SQL Server

Question

I have been using SET keyword to assign the values to parameters declared in SQL Server something like,

Declare @StudentName Nvarchar(max) 
SET @StudentName='ABC'

I'm not sure how to write this if they are more than 1 student name.

Say student names are being fetched from another Select Statement (Select student_name from student_tbl)

Will that be

SET @StudentName= 'Select student_name from student_tbl'

I'm not sure how SET works in case of multiple values.

Answer 1

Multiple values shouldn't be assigned to a scalar variable. It makes no sense

You need a table variable (or temporary table).

Declare @StudentName TABLE (sname Nvarchar(max));
INSERT @StudentName (sname) VALUES ('ABC'),('DEF'),('PQR'),('XYZ');

You can JOIN etc to this as you would a normal table. Or use it in an IN clause

WHERE
    X.SomeCol IN (SELECT sname FROM @StudentName)

Edit, after comments

INSERT @StudentName (sname) 
SELECT ... -- the other query here