-- 2 different delete statements:
-- the rowcount method is deprecated in future versions.
-- the alternative is to use "SELECT TOP" used in the CTE

-- Both still work the same way; if the @@rowcount (returns the number of rows affected by the last statement) is 0 then there are no more rows to delete
-- we loop through as there are billions of rows to delete, so if script fails for whatever reason we only lose last run

SET NOCOUNT ON

--variables which we are deleting by
DECLARE @ProductId nvarchar (32)
DECLARE @SalesId int

--store the name of the table we are going to delete
DECLARE @tblname NVARCHAR (128)

--we require different delete statements for each schema
--in our database. This works quite nicely as we don't have to get
--clever with deciding which delete statement to run
DECLARE @schema nvarchar (32)

--counting through total number of clients in the tables
--could use a cursor here but either way is ok
DECLARE @count INT

--@sql variable to execute by
DECLARE @sql nvarchar (MAX)

-- checking if temp table exists form previous run and dropping if it does
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#tablesToDelete'))
DROP TABLE #tablesToDelete;

-- put list of tabels you want to delete from here
CREATE TABLE #tablesToDelete ([TblName] nvarchar (128))
INSERT INTO #tablesToDelete VALUES('Sales.SalesNumbers')
INSERT INTO #tablesToDelete VALUES('Products.Products')
INSERT INTO #tablesToDelete VALUES('Bob.NoSchema')

SELECT @count = COUNT (*) from #tablesToDelete
while @count > 0
BEGIN

SELECT TOP 1 @tblname = [TblName] from #tablesToDelete

SET @schema = SUBSTRING (@tblName, 1, CAST (CHARINDEX ('.', @tblname) -1 AS INT))
SET @SalesId = 85
SET @ProductId = '2, 21, 9, 20'

IF @schema = 'Sales'
BEGIN
set @sql = '
USE MontyDev
DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 1000000
delete from '+@tblname+' where tagId < '+CAST(@SalesId AS nvarchar)+'
SET @rowcount = @@rowcount
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END'
EXEC (@sql)
END
ELSE IF @schema = 'Products'
BEGIN
set @sql = '
USE MontyDev
DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
;WITH CTE AS
(
select top 1000000 * from '+@tblname+' where clientId NOT in ( '+CAST(@ProductId AS nvarchar)+')
and tagId < '+CAST(@SalesId AS nvarchar)+
'
order by
[ProductId] ASC,
[SalesId] ASC

)
DELETE FROM CTE
SET @rowcount = @@rowcount
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END

'
PRINT @tblname
EXEC (@SQL)
END
ELSE PRINT 'Delete query not defined for schema '+@schema+'!'
DELETE FROM #tablesToDelete where [TblName] = @tblname
SET @count = @count - 1
END

SET NOCOUNT OFF