STRING_SPLIT in T-SQL

SELECT tooth INTO #teeth FROM STRING_SPLIT(@cavities, ',')

 
 

@cavities above would be perhaps an nvarchar(max) variable handed into a stored procedure. The above will break a string of ids separated by commas (without spaces) into an on-the-fly table that has one column named "tooth" that may then be queried like so:

SELECT * FROM Molars yin
WHERE yin.ToothId in (SELECT yang.tooth FROM #teeth yang)

 
 

Addendum 10/16/2019: The tooth column name here probably won't cut it. I think the nameless column has to be named "value" instead like so:

SELECT [value] INTO #attesters
      FROM STRING_SPLIT((SELECT TOP 1 AttestedBy FROM
      @ReportingUnitsHalfwayFlattened WHERE AppSecurityRUKey =
      @AppSecurityRUKey), ',')
SELECT COUNT(*) From #attesters WHERE [value] = @AppSecurityRUKey