Syntax to create store proc to delete huge receords

Question

I need to create a store procedure with a loop that takes two parameter numOfRowsToDelete and delayTime so that store proc delete the that numOfrows and wait for the delayTime and run again. How do I do it. I am new to database. Please help me

Madhanmohan Devarajan · Answer

Gaurav Would like to understand few things. Whether you want to delete the records in a table based on the count of rows (parameter)? irrespective of which 10 or 40 records to be deleted. Below link has the sample stored procedure to delete the records (result of search query) from the table. https://technet.microsoft.com/en-us/library/ms189634(v=sql.105).aspx Hope it will solve your issue.

Nigel Fernandes · Answer

Another approach you can take is schedule the deletion of rows during a time when database is not used. Example during Night. Then you would not need to implement any extra logic. But use this only if deletion can be done later.

Nepethya Rana · Answer

CREATE PROCEDURE spDeleteRecord @DeleteBatchSize INT @DelayTime DATETIME AS BEGIN SET NOCOUNT ON; DECLARE @DeleteRowCount INT SET @DeleteRowCount = 1 WHILE (@DeleteRowCount > 0) BEGIN BEGIN TRANSACTION DELETE TOP(@DeleteBatchSize) SchemaTableName; SET @DeleteRowCount = @@ROWCOUNT; PRINT @DeleteRowCount; COMMIT WAITFOR DELAY @DelayTime END END GO

Dhananjay Kumar · Answer

https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes