Dynamic T-SQL to DELETE TOP and Loop Through Entire Table (Help!)

[smedvid]

A new security rule is now in place where TRUNCATE TABLE must be replaced with DELETE TOP & a Loop. TRUNCATE TABLE appears to have some security risks per the DBA Team? Of course, we need to replace the 1 line statement with logic we develop for DELETE TOP & Loop.

I am getting an error message "Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.".

What am I missing here? Pseudo Code below...

--I run code in a test block like below and it appears to work!
--Same "exact" code in an SP fails and error message appears.

--TRUNCATE TABLE Prohibited per DBA for security reasions
-- Delete in Batches Only Alternative!  
DECLARE @LoopCnt	INT
DECLARE @BatchSize	INT
DECLARE @CurRowCount	INT   
DECLARE @LCAT	NVARCHAR(4)
DECLARE @SQL NVARCHAR(2000)

SET @LCAT = 'R1D1'   -- Passed into SP normally and will change!
SET @CurRowCount = 0
SET @BatchSize = 25000

SET @SQL = N'SELECT @CurRowCount = COUNT(*) FROM ADATA.tblData_' + @LCAT 
PRINT @SQL
PRINT @CurRowCount

EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@CurRowCount INT OUTPUT', 
        @CurRowCount = @CurRowCount OUTPUT 

--HERE IS WHERE IT BOMBS OUT! 

PRINT @CurRowCount

--Code to Delete in Loop!!!  
--SET @LoopCnt = CEILING(@CurRowCount/@BatchSize) + 1

---- Loop and delete records in small bits till the LoopCnt ends.
--WHILE @LoopCnt > 0
--	BEGIN
--		SET @SQL = 'DELETE TOP (' + LTRIM(RTRIM(CONVERT(nvarchar(10), @BatchSize))) + ') FROM ADATA.tblData_' + @CAT + ' '  
--		EXEC (@SQL)
--		SET @LoopCnt = @LoopCnt - 1
--	END

Thanks in Advance!!!

Steve Medvid
IT Consultant & Web Master

 

[djj55]

Do you mean @CAT or @LCAT in your delete statement?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[smedvid]

@LCAT --- I changed the source code as to not reveal any meaningful details or references....

Steve Medvid
IT Consultant & Web Master

 

[smedvid]

GOT IT!

In SP I had:
DECLARE @SQL VARCHAR(2000)

In Stand-Alone Code I Had:
DECLARE @SQL NVARCHAR(2000)

Something that little can cause problems... Well 2 hours well spent to learn the lesson that I will hopefully not repeat!




Steve Medvid
IT Consultant & Web Master

 

[djj55]

I had the right idea wrong place

I also have the problem of typos when obfuscating my code.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!