Converting String List into Int List in 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)ASBEGIN    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    RETURNEND

Call it like this:

SELECT      *FROM        TableWHERE status IN (SELECT * from fnStringList2Table(@statuslist))

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)

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))RETURNEND

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

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