How to convert string array to int array in T-Sql

Question

I got a sql function that returns a string array,..., now I want to convert it into an int array,

Msg 245, Level 16, State 1, Line 14 Conversion failed when converting the varchar value ',' to data type int.

My code :

DECLARE @ListZoneId as varchar(200);
SET @ListZoneId = [dbo].[CMS_fGetListOfZoneIdByParentZoneId](1)

//sql function return string array : '1,22,30,14'

declare @tempTb table
(
TempId int
)

while charindex(',',@ListZoneId) > 0
begin
insert into @tempTb select  substring(@ListZoneId,1,1)

SET @ListZoneId = substring(@ListZoneId,3,len(@ListZoneId))
end


SELECT  COUNT(*) from Shop as s inner join ShopInZone as z on s.Id=z.ShopId inner join Zone as zo on zo.Id  = z.ZoneId 
where  z.ZoneId IN  ( select * from @tempTb)

Something isn't making sense to me here. I can't tell what is your function and what isn't. But I think the problem is that you are trying to use a comma delimited string in your IN clause. Effectively IN ('1,2,3,4'). Assuming @tempTb has just that one value you have an issue where this won't work. Using delimited list like this in sql server is a real to deal with. I would recommend not doing that to yourself. If you must do this you will have to split your delimited list. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 17 '16 at 16:41
You might have a look here [section "Dynamic IN Statement"](http://stackoverflow.com/a/33658220/5089204) — Shnugo, Mar 17 '16 at 17:12
It migth be worth to show us the implementation of your function `CMS_fGetListOfZoneIdByParentZoneId`. This could return a user defined `TYPE` or maybe an XML (e.g. ``) which would make it much easier to be used as derived table... Anyway you should avoid loops... — Shnugo, Mar 17 '16 at 17:18

How to convert string array to int array in T-Sql

0 Answers0