How to pass comma delimited string to where clause in TSQL?-CodePudding

I have the following query. When I run it, I get the errors. What is the best way to do this?

declare @Slots varchar(50)
set @Slots = '1,2'
select * from Schedules where SlotID in (@Slots)

Error:

Msg 245, Level 16, State 1, Line 5 Conversion failed when converting the varchar value '1,2' to data type int.

CodePudding user response：

You can also JOIN with STRING_SPLIT table-valued function that splits a string into rows of substrings, based on a specified separator character. See following example:

DECLARE @Schedules TABLE(SlotID int);
INSERT @Schedules VALUES (1),(2),(3),(4);

DECLARE @Slots varchar(50);
SET @Slots = '1,2';

SELECT *
FROM @Schedules S
JOIN STRING_SPLIT(@Slots, ',') ON S.SlotID=value

CodePudding user response：

SQL Server does not support macro substitution. You can, however, use string_split()

declare @Slots varchar(50)
set @Slots = '1,2'

Select * 
 From  Schedules 
 Where SlotID in (select Value from string_split(@Slots,','))