TSQL - How to iterate a list of strings

database-cursor, t-sql

Solution

declare @jobNames table(Id int, JobName nvarchar(100))

insert @jobNames values
    (1, 'JobName1'),
    (2, 'JobName2'),
--
    (10, 'JobName10')

while exists(select 1 from @jobNames)
begin
    declare @id int, @name nvarchar(100)
    select top 1 @id = Id, @name = JobName from @jobNames
    delete from @jobNames where Id = @Id

    -- Do stuff here
end

Problem

I want to create a procedure that will insert all my jobs to the DB. (a. All my jobs have equal characteristics. b. SSDT doesn't support jobs code management) Now, I thought to create a script to insert all of them and as a c# developer I thought I need to initialize a list with their names. I discovered while googling that the way to do it is with an in memory table and the best I could come with is this. ``` declare @jobsNames table(Id int, JobName nvarchar(100)) insert into @jobsNames (Id,JobName) select 1,'JobName1' union select 2,'JobName2' union ...... BEGIN TRANSACTION DECLARE JobsCursor CURSOR FOR SELECT JobName FROM @jobsNames OPEN JobsCursor FETCH NEXT FROM JobsCursor INTO @JobName WHILE @@Fetch_status = 0 BEGIN .. do stuff FETCH NEXT FROM JobsCursor INTO @JobName WHILE @@Fetch_status = 0 END COMMIT TRANSACTION ``` Question - Is this the shortest/recommended way? (It seems a hellotof code for a foreach)

Original source