Casting BIGINT as Varchar Err

Question

I have a SP like below for the, if I want to pass multiple ids like 1111, 2222 I am getting error even If I cast ID as varchar(100)

BEGIN
Declare @ID BIGINT
SELECT @ID = 1111

SELECT * INTO TAB1 FROM TAB2 WHERE ID IN @ID 
..
..
SELECT * INTO TABn FROM TABm WHERE ID IN @ID 
END

I tried below it didn't worked. with the above code I am forced to use only one value e.g. 1111

BEGIN
Declare @ID VARCHAR(100)
SELECT @ID = ('1111','2222')

SELECT * INTO TAB1 FROM TAB2 WHERE CAST(ID as VARCHAR(100)) IN @ID 
..
..
SELECT * INTO TABn FROM TABm WHERE CAST(ID as VARCHAR(100)) IN @ID 
END

Answer 1

SELECT @ID = ('1111','2222') is not correct. Try the following script if your SQL server is 2016, 2017 or 2019:

DECLARE @ID VARCHAR(100)
SET @ID = '1111,2222';
SELECT value FROM STRING_SPLIT(@ID, ',');

For you case:

SELECT * INTO TAB1 FROM TAB2 WHERE ID IN (SELECT value FROM STRING_SPLIT(@ID, ','))

Answer 2

Please see:
https://www.brentozar.com/archive/2020/02/how-to-pass-a-list-of-values-into-a-stored-procedure/

Answer 3

Thanks GuoxiongYuan I tested this, it worked.