In order to use input parameter to stored procedure or any other internal variable of varchar type within SQL IN statement, do the following.
Create a function which wchich will take string (varchar variable) as input and converts it into table of values.
CREATE FUNCTION dbo.StringToTable
(
@String VARCHAR(200),
@Delimiter VARCHAR(5)
)
RETURNS @SplitValues TABLE
(
Id VARCHAR(200)
)
AS
BEGIN
DECLARE @SplitLength INT
WHILE LEN(@String) > 0
BEGIN
SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)
INSERT INTO @SplitValues
SELECT SUBSTRING(@String,1,@SplitLength)
SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
END
RETURN
END
User this function in SQL IN statement as below. Considering input variable name as @InputString
DECLARE @InputString varchar(50)
SET @InputString = '1,2,3,4'
SELECT * FROM Employees WHERE EmpId IN (SELECT Id FROM dbo.StringToTable(@InputString,','))