CODEWITHSUNDEEP
sql-server
K.Paul
K.Paul

Reputation: 23

Loop through an comma separated array to insert in a table SQL Server

Basically this is what I want to achieve. I am receiving both UserId and ProductId as parameters on my stored procedure, which inserts those values into a table called UserProduct. The ProductId is supplied as a comma separated array parameter and the UserId as a single integer value.

The following is the result set sample of what I want to achieve after select statement from this table. Is this achievable? Thanks in advance. 

UserId  ProductId
-------------------
817      7 
817      5
817     33
798      6
798     12
798      2
798      4
888      5
...    ...

Upvotes: 2

Views: 5545

Answers (2)

Sheriff
Sheriff

Reputation: 868

Kindly find this..

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,'

--the value list string must end with a comma ','
--so, if the last comma it's not there, the following IF will add a trailing comma to the value list
IF @valueList NOT LIKE '%,'
BEGIN
    set @valueList = @valueList + ','
END


set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    --SELECT @pos, @len, @value /*this is here for debugging*/

    PRINT @value
    --Here is you value
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE

    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

Upvotes: 2

Fer
Fer

Reputation: 1992

You can try below TSQL. 

-- supposing @ProductID comma seperated values - varchar
DECLARE @ix INT
DECLARE @value varchar(25)

WHILE PATINDEX('%,%', @ProductId) <> 0 
BEGIN
     SELECT  @ix= PATINDEX('%,%',@ProductId)
     SELECT  @value= LEFT(@ProductId, @ix- 1)
     SELECT  @ProductId= STUFF(@ProductId, 1, @ix, '')

    INSERT INTO table (UserId,ProductId) VALUES(@UserId, @value)
END

In fact, you should consider taking productIDs as an array.

Upvotes: 2

Related Questions