SQL Server Compare my parameter in the where clause inside while condition in procedure

Question

I'm trying to load a table with a procedure with while condition. But I'm failing in compare the Strings received such as a vector in the where clause.

I'm receiving 'Canada,Brazil,Germany' and need to compare with a CountryListSource table that have all countries. I'm using merge to update or insert the Target Table (maybe another condition it will more simple). So, my target table is empty but the stored procedure when run with the parameters not show errors, just run but not insert any data.

exec ProcessString 'Canada,Brazil,Germany'

This is the stored procedure code:

CREATE PROCEDURE ProcessString
    @String VARCHAR(MAX)
AS
BEGIN
    DECLARE @Array INT
    DECLARE @Aux VARCHAR(MAX)
    
    SET @Array = CHARINDEX(',', @String)
    
    WHILE @Array > 0
    BEGIN
        SET @Aux = LEFT(@String, @Array - 1)
        
          ;WITH cte AS
          (
              SELECT DISTINCT
                  cl.Country, 'N/A' AS CountryISOCode, 2 AS UserId
              FROM
                  [dbo].[CountryListSource] AS cl
              WHERE
                  1 = 1
                  AND cl.City = @String
          )
          MERGE INTO [dbo].[CountryListSourceTarget] AS target
          USING cte AS source ON source.Country = target.Country 

          -- Update
          WHEN MATCHED AND (NOT EXISTS(SELECT target.Country, target.CountryISOCode, target.UserId
                                       INTERSECT
                                       SELECT source.Country, source.CountryISOCode, source.UserId
                                      )) 
              THEN UPDATE
                   SET target.UserId = source.UserId,
                       target.Country = source.Country,
                       target.CountryISOCode = source.CountryISOCode, 
                       target.InsertDate = target.InsertDate,
                       target.UpdateDate = GETDATE()
          -- Insert
          WHEN NOT MATCHED BY TARGET
              THEN
                  INSERT (UserId, Country, CountryISOCode, InsertDate, UpdateDate)
                  VALUES (source.UserId, source.Country,
                          source.CountryISOCode, GETDATE(),
                          '1900-01-01 00:00:00.000');
        PRINT 'Processing string: ' + @Aux
        
        SET @String = SUBSTRING(@String, @Array + 1, LEN(@String) - @Array)
        SET @Array = CHARINDEX(',', @String)
    END
    
    -- Process the last string (or unique without comma)
    PRINT 'Processing string: ' + @String
END

Answer 1

I'm not sure why you think you need a loop here. You can use STRING_SPLIT in a single statement.

You should also add a SERIALIZABLE locking hint.

WITH cte AS
(
    SELECT
        cl.Country, 'N/A' AS CountryISOCode, 2 AS UserId
    FROM
        STRING_SPLIT(@String, ',') s
    JOIN dbo.CountryListSource AS cl ON cl.City = s.value
    GROUP BY
        c.Country
)
MERGE INTO dbo.CountryListSourceTarget AS target WITH (SERIALIZABLE)
USING cte AS source ON source.Country = target.Country 
.....

To be honest you are probably better off using a Table Valued Parameter anyway, rather than splitting strings.