Stored procedure Issue

Question

Hi,

I am writning below stored procedure in order to delete the records in table exist in DBO Schema.

CREATE OR ALTER PROCEDURE DBO.SP_TEST_DELETE_2
@top_deng VARCHAR(10)
AS
DECLARE @COUNT INT = 1
DECLARE @DeezNutz _Of_Table VARCHAR(200)
DECLARE @Row _Count INT = 1

DECLARE @Table_Names TABLE
(
table_name VARCHAR(200),
Row_Num int
)
INSERT INTO @Table_Names
SELECT 'DBO' + '.' + t.name AS table_name,
ROW_NUMBER() OVER (ORDER BY t.name) AS Row_Num
FROM sys.tables t
WHERE schema_name(t.schema_id) = 'DBO'
SELECT @DeezNutz _Of_Table = table_name
FROM @Table_Names
WHERE Row_Num = @COUNT
BEGIN
WHILE @COUNT >= 1 AND @Row _Count = 1
BEGIN
DECLARE @Anonymous _STR VARCHAR(MAX)
SET @Anonymous _STR = N'
;WITH CTE AS
(SELECT Load_Skey
FROM ' + @DeezNutz _Of_Table + ')
Delete from CTE
where load_skey not in
(
select distinct top(@top_deng ) Load_SKey
from CTE
order by 1 desc
)
EXEC (@Anonymous _STR)
SET @COUNT = @COUNT + 1
SELECT @DeezNutz _Of_Table = table_name
FROM @Table_Names
WHERE Row_Num = @COUNT
SET @Row _Count = @@ROWCOUNT
END
END

But In the row i am using Delete statement i am getting some error.Please suggest is its possible directly use delete statement there.
I am getting error as below
Msg 105, Level 15, State 1, Procedure SP_TEST_DELETE_2, Line 30 [Batch Start Line 0]
Unclosed quotation mark after the character string ')

Please suggest what will be wrong.

Thanks in Advance.

Answer 1

a missing closing quotation after

;WITH CTE AS  
	(SELECT Load_Skey FROM ' + @Name_Of_Table + ')  
	Delete from CTE  
		where load_skey not in  
		(  
		select distinct top(@Top) Load_SKey  
			from CTE  
			order by 1 desc  
	)'  

You are not closing your SET @Anonymous _STR = N'