IN doesn't work that way. An IN takes each value in the list (in your case the variable @Region) and converts them to a series of OR statements.

Both of your selects have a single value in the IN clause so they are essentially this:

SELECT * FROM TSDBA.TS_LOCATION WHERE UPPER(LTRIM(RTRIM(REGION_ID))) = 'MG' ;

SELECT * FROM TSDBA.TS_LOCATION WHERE UPPER(LTRIM(RTRIM(REGION_ID))) = ' ''MG'' ,''SF'' '

Expressed like that, I'm sure you can see why the second is returning no data, Either change your design, or look up the Delimited8kSplit function and use that.

btw, the functions on the Region_ID column ensure that these two queries will execute with a table scan and hence will perform terribly on larger tables. Unless your DB is case-sensitive, the UPPER has no purpose. SQL ignores trailing spaces so the RTRIM has no purpose at all. If you have leading whitespace in your columns consider fixing the data rather than hobbling the query performance.