Q&A for members of the workforce navigating the professional setting
DECLARE @valueList varchar(8000) DECLARE @pos INT DECLARE @len INT DECLARE @value varchar(8000) DECLARE @ParentID VARCHAR(5000) = '' DECLARE @ParentIDs VARCHAR(5000) = '' SET @valueList = '730145,506,Price And Qty Update,4.00,243,Quantity Update' --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) IF(@ParentID = '') begin SET @ParentID = @value end else IF(@ParentIDs = '') begin SET @ParentIDs = @value end --SELECT @pos, @len, @value /*this is here for debugging*/ --select replace (@value, ',','''') --select @Value set @pos = CHARINDEX(',', @valueList, @pos+@len) +1 end