Convert string list to internal list in SQL

Question

I have nvarchar (MAX) in my stored procedure that contains a list of int values, I did it like it is not possible to pass an int list to my stored procedure , but now I get the problem, since my data type is int, and I want to compare the list lines. Is there any way I can do the same?

---myquerry----where status in (@statuslist) 

but the status list now contains string values, not int, so how do I convert them to INT?

UPDATE:

 USE [Database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP] ( @FromDate datetime = 0, @ToDate datetime = 0, @ID int=0, @List nvarchar(MAX) //This is the List which has string ids// ) 

A.S. SET FMTONLY OFF; DECLARE @sql nvarchar (MAX), @paramlist nvarchar (MAX)

 SET @sql = 'SELECT ------ and Code in(@xList) and -------------' SELECT @paramlist = '@xFromDate datetime,@xToDate datetime,@xId int,@xList nvarchar(MAX)' EXEC sp_executesql @sql, @paramlist, @xFromDate = @FromDate ,@ xToDate=@ToDate ,@ xId=@ID ,@ xList=@List PRINT @sql 

Therefore, when I implement this function, which is broken, I cannot specify a character or separator, since it does not accept it as (@List, ',').

or (',' + @List + ',').

Answer 1

You can send an int list to your stored procedure using XML parameters. Thus, you no longer have to solve this problem, and it is a better and cleaner solution.

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

or check out 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 own way, you can 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 

Name it as follows:

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

Answer 2

You can also work with a string list. I always do that.

 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) 

Answer 3

In fact, you can send a list of int values ​​to your procedure by creating a User Defined Table Type . However, this means a lot of work to populate the table parameter.

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

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

Answer 4

You can do this using a sql function that will return you an integer array. It would be great if you passed the @Delimiter line, split into your stored procedure, which could later be processed correctly.

Write one function to split the data as follows

 ALTER FUNCTION [YourSchema].[SplitValues] (@StringArray NVARCHAR(MAX), @Delimiter NVARCHAR(10)) RETURNS @ResultedValues table ( ResultValue INT ) AS BEGIN 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 as below, I use (,) like @Delimiter here

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

Answer 5

here is an example of how to do this, and a link for more information

 ALTER FUNCTION iter_intlist_to_tbl (@list nvarchar(MAX)) RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, number int NOT NULL) AS BEGIN DECLARE @startpos int, @endpos int, @textpos int, @chunklen smallint, @str nvarchar(4000), @tmpstr nvarchar(4000), @leftover nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen)) SET @textpos = @textpos + @chunklen SET @startpos = 0 SET @endpos = charindex(' ' COLLATE Slovenian_BIN2, @tmpstr) WHILE @endpos > 0 BEGIN SET @str = substring(@tmpstr, @startpos + 1, @endpos - @startpos - 1) IF @str <> '' INSERT @tbl (number) VALUES(convert(int, @str)) SET @startpos = @endpos SET @endpos = charindex(' ' COLLATE Slovenian_BIN2, @tmpstr, @startpos + 1) END SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos) END IF ltrim(rtrim(@leftover)) <> '' INSERT @tbl (number) VALUES(convert(int, @leftover)) RETURN END -- ############################ Example ############################ --CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS --SELECT P.ProductName, P.ProductID --FROM Northwind..Products P --JOIN iter_intlist_to_tbl(@ids) i ON P.ProductID = i.number --go --EXEC get_product_names_iter '9 12 27 37' -- ############################ WICHTIG ############################