Converting String List into Int List in SQL

You can work with string list too. I always do.

declare @statuslist nvarchar(max)
set @statuslist = '1, 2, 3, 4'

declare @sql nvarchar(max)
set @sql = 'select * from table where Status in (' + @statuslist + ')'
Execute(@sql)

It is possible to send an int list to your stored procedure using XML parameters. This way you don't have to tackle this problem anymore and it is a better and more clean solution.

have a look at this question: Passing an array of parameters to a stored procedure

or check this code project: http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

However if you insist on doing it your way you could use this function:

CREATE FUNCTION [dbo].[fnStringList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item int
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN
END

Call it like this:

SELECT      *
FROM        Table
WHERE status IN (SELECT * from fnStringList2Table(@statuslist))

You can do this by using sql function which will return you an integer array.. It would be great if you pass @Delimiter separated string to your stored procedure which could be processed properly afterwards.

Write one function to split the data as following

CREATE FUNCTION [dbo].[SplitValues] (@StringArray NVARCHAR(MAX), @Delimiter NVARCHAR(10)) 
RETURNS @ResultedValues table 
(
ResultValue INT
) 
AS 
BEGIN       
  DECLARE @Tokens TABLE(Token nvarchar)         
  DECLARE   @String nvarchar

  WHILE (CHARINDEX(@Delimiter,@StringArray)>0)
   BEGIN 
    INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(SUBSTRING(@StringArray,1,CHARINDEX(@Delimiter,@StringArray)-1))))
    SET @String = SUBSTRING(@StringArray,
    CHARINDEX(@Delimiter,@StringArray)+LEN(@Delimiter),LEN(@StringArray))
   END 
INSERT INTO @ResultedValues (ResultValue ) VALUES ( CAST(LTRIM(RTRIM(@String)) AS INT))
RETURN
END

And then use it like following, i am using (,) as @Delimiter here

SELECT ResultValue [YourSchema].[SplitValues](@statuslist,',')

Actually, you can send the list of int values to your procedure by creating a User Defined Table Type. However, this implies more work in order to populate the table parameter.

In your case, you can use the sp_executesql stored procedure to achieve what you want like this:

declare @statement nvarchar(4000) = '----your query---- where status in (' 
+ @statusList +')'
sp_executesql @statement