looping through Comma Separated Values in SQL Server

Question

I need to loop through each value present in the variable @columnNames and use it in a dynamic sql. Below is the code.

DECLARE @columnNames NVARCHAR(MAX), @dSql NVARCHAR(MAX)

SEt @columnNames = 'SCHEDULEDSTART, SCHEDULEDEND'

WHILE (LEN(@columnNames) > 0)
BEGIN
    SELECT left(@columnNames, charindex(',', @columnNames+',')-1)
    print @columnNames
    SET @dSql = 'ALTER TABLE NAMS.AssetActivity ALTER COLUMN ' + @columnNames + ' DATETIME2(7)'
    PRINT (@dSql)
    set @columnNames = stuff(@columnNames, 1, charindex(',', @columnNames+','), '')
END

In the output the 1st query is messed up. The output that I'm getting is listed below:

(1 row affected)
ALTER TABLE NAMS.AssetActivity ALTER COLUMN SCHEDULEDSTART, SCHEDULEDEND DATETIME2(7)

(1 row affected)
ALTER TABLE NAMS.AssetActivity ALTER COLUMN  SCHEDULEDEND DATETIME2(7)

A help is going to be very much appreciated. Thanks in Advance :)

Answer 1

Adding to Garbriele's answer, your script would have to look like this:

DECLARE
    @columnNames    NVARCHAR(MAX)
,   @dSql           NVARCHAR(MAX) = ''

SET @columnNames = 'SCHEDULEDSTART, SCHEDULEDEND'

SELECT
    @dSql = @dSql + 'ALTER TABLE NAMS.AssetActivity ALTER COLUMN ' + LTRIM(RTRIM(Value)) + ' DATETIME2(7)'+char(10)
FROM    STRING_SPLIT(@columnNames, ',')

PRINT   (@dSql)
--EXEC  (@dSql)

Which gives you the following results:

See DEMO

Answer 2

From SQL Server 2017 you can use STRING SPLIT function:

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15