I have a table including:
ID Name Period0Id Period1Id Period2Id
declare @check int = 1;
Select ID, Name, StatusId = Period + @check + Id -- @check is the parameter
Your table looks like it is not in first normal form.
Instead of three columns for Period0Id to Period2Id you could have a column for PeriodIndex with values of (0,1,2) and a single column for PeriodId and then it would be just a
WHERE PeriodIndex = @Check
You can't select a column using string interpolation with a variable as you are attempting. You can use dynamic SQL to create the SQL String dynamically. Or simply hardcode the options if they all have the same dataype.
Select ID, Name, StatusId = CASE @Check WHEN 0 THEN Period0Id WHEN 1 THEN Period1Id WHEN 2 THEN Period2Id END From mytable