Cursor and Array in Stored Procedure

Stored Procedure

standard startup lines

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE isp_OrgStructureInsert
(
	@Description nvarchar(255), 
	@ExternalRef nvarchar(255),
	@EffectiveDate int,
	@OrgLevelCatID int,
	@OrgStructureID int,
	@ParentID int,
	@ProfileID int,
	@WorkAreaAtrributeISs_CSV NVARCHAR(MAX),    -- This is the array parameter in CSV format
	@OrgLevelID int OUTPUT
)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @OrgLevelID = MAX(OrgLevelID) + 1 FROM isOrgLevels

 INSERT INTO isOrgLevels (OrgLevelID, EffectiveFrom, EffectiveTo, Description, OrgLevelCatID, ExternalRef, ResourceKey)
	VALUES (@OrgLevelID, @EffectiveDate, NULL, @Description, @OrgLevelCatID, @ExternalRef, NULL)

 INSERT INTO isOrgStructures (OrgStructID, OrgLevelID, EffectiveFrom, EffectiveTo, ParentID)
    VALUES (@OrgStructureID, @OrgLevelId, @EffectiveDate, NULL, @ParentID) 

Nothing special so far. From this point forward I'm declaring a cursor for the CSV string and convert this string to table rows by using fn_List2Table function. You may find this function codes below.

	-- WorkAreaAttributeIDs
	DECLARE @AttributeID INT
	DECLARE CrsAttrib CURSOR FOR 
	SELECT CAST(item AS INT) AS AttribID
	FROM dbo.fn_List2Table(@WorkAreaAtrributeISs_CSV,',')
  
	OPEN CrsAttrib
	FETCH NEXT FROM CrsAttrib
	INTO @AttributeID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO isWorkAreaAttributeIDs VALUES (@AttributeID, @OrgLevelId, 1)
		FETCH NEXT FROM CrsAttrib
		INTO @AttributeID
	END
	CLOSE CrsAttrib
	DEALLOCATE CrsAttrib
 END
 GO

Function

CREATE FUNCTION [dbo].[fn_List2Table]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
@ParsedList TABLE
(
item VARCHAR(MAX)
)
AS
BEGIN
 DECLARE @item VARCHAR(MAX), @Pos INT
 SET @List = LTRIM(RTRIM(@List))+ @Delim
 SET @Pos = CHARINDEX(@Delim, @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 VARCHAR(MAX)))
  END
  SET @List = RIGHT(@List, LEN(@List) - @Pos)
  SET @Pos = CHARINDEX(@Delim, @List, 1)
 END
RETURN
END
GO