Sql-server – Using Cursor to loop through a Table variable in SQL Server

sql serversql-server-2008

I have a parameter in the Stored Prod which gets some data in the format 

1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,8/1/2018-9/1/2018,9/1/2018-10/1/2018,
10/1/2018-11/1/2018,11/1/2018-12/1/2018,12/1/2018-12/31/2018

I have a function which splits the data based on , character and stores it in a table variable as shown below

declare @SPlitDates table(ItemNumber int,Item nvarchar(max))
insert into @SPlitDates
select * from dbo.SPlitFunction(@RequestData, ',')

After this I have to perform certain operations on the data range so I use cursors to loop through the temp table as shown below

DECLARE cur CURSOR FOR SELECT Item FROM @SPlitDates order by ItemNumber
OPEN cur
FETCH NEXT FROM cur INTO @monthStart
WHILE @@FETCH_STATUS = 0 BEGIN
--Some operation
End

The max data points that I will get in the temp table is the date range for 12 months.

My question is that could I be using something else apart from Cursors to improve performance or it doesn't matter when the dataset is really this small.

Thanks

Best Answer

If possible, just treat this as a set and consider if you can rewrite the --Some operation as a single batch.

It may require you to get smart with joins to other tables based on the date ranges - or perhaps you could split the date range further into StartDate and EndDate columns before doing your additional processing which might help with joins

Related Question