How do I execute a stored procedure in batches?

Question

I inherited a NopCommerce project from a previous developer. They left me with a Customer table with 350 million guest users.

I'm executing a stored procedure to delete the guest users but it's been running for 15 hours now. DTU is constantly hitting 100% and the site is crashing. Is there a way to run this in batches? Say 5 million records per run?

Here's the SQL:

DECLARE @return_value int,
    @TotalRecordsDeleted int

EXEC    @return_value = [dbo].[DeleteGuests]
    @CreatedFromUtc = NULL,
    @CreatedToUtc = NULL,
    @TotalRecordsDeleted = @TotalRecordsDeleted OUTPUT

SELECT  @TotalRecordsDeleted as N'@TotalRecordsDeleted'

SELECT  'Return Value' = @return_value

GO

Answer 1

Well - turns out, there is a pretty obvious way of splitting up the job. I'll post it below in case someone else runs into the same issue.

By setting a date range, you can run this in steps without bogging down the server for hours or days.

DECLARE @return_value int,
    @TotalRecordsDeleted int

EXEC    @return_value = [dbo].[DeleteGuests]
    @CreatedFromUtc = N'2022-04-01', -- Start date
    @CreatedToUtc = N'2022-04-15', -- End Date
    @TotalRecordsDeleted = @TotalRecordsDeleted OUTPUT

SELECT  @TotalRecordsDeleted as N'@TotalRecordsDeleted'

SELECT  'Return Value' = @return_value

GO