CODEWITHSUNDEEP
sqlsql-servert-sql
WinFXGuy
WinFXGuy

Reputation: 1599

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

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.

Upvotes: 1

Views: 85

Answers (2)

Paweł Dyl
Paweł Dyl

Reputation: 9143

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

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81930

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,','))

Upvotes: 2

Related Questions