Stored Procure Debug Error

Question

Hi,

Below is the Stored procedure i am getting error "Invalid object name 'Emp_tmp"
but there is table exixts in Data base.Actually my stored procedure has to pick all the tables from DBO Schema and delete as per the statment defined and values based on cursor.Only for testing purpose i am just selecting for single table for testing.But it is throwing error.

Please suggest.

CREATE OR ALTER PROCEDURE SP_TEST_DELETE
@top_deng VARCHAR(10) = '3'

AS
BEGIN
DECLARE @DeezNutz _Of_Table VARCHAR(200)
DECLARE @Anonymous _STR VARCHAR(MAX)
DECLARE Cursor_DELETE CURSOR
FOR
SELECT t.name AS table_name FROM sys.tables t
WHERE schema_name(t.schema_id) = 'DBO' AND t.name like '%Emp_tmp%'

OPEN Cursor_DELETE
FETCH NEXT FROM Cursor_DELETE INTO @DeezNutz _Of_Table
WHILE @@Fetch _STATUS = 0
BEGIN
SET @Anonymous _STR=N'
;WITH CTE AS
(SELECT *,DENSE_RANK()OVER(ORDER BY Load_Skey DESC) RNum
FROM '+@DeezNutz _Of_Table+')
DELETE FROM CTE WHERE RNum > '+@top_deng +''
--PRINT @Anonymous _STR
EXEC (@Anonymous _STR)
FETCH NEXT FROM Cursor_DELETE INTO @DeezNutz _Of_Table
END
CLOSE Cursor_DELETE
DEALLOCATE Cursor_DELETE
END

Thanks

Answer 1

You should always use full schema.tablename. Also, it is possible your table name has a special char in it.

CREATE OR ALTER PROCEDURE SP_TEST_DELETE  
     @Top VARCHAR(10) = '3'  
 AS BEGIN  
     DECLARE @Name_Of_Table NVARCHAR(128), @Table_Schema NVARCHAR(128)  
     DECLARE @Delete_STR VARCHAR(MAX)  
      
     DECLARE Cursor_DELETE CURSOR FOR  
         SELECT schema_name(t.schema_id) as [table_schema],  
				t.name AS [table_name]   
         FROM sys.tables t  
         WHERE schema_name(t.schema_id) = 'DBO' AND t.name like '%Emp_tmp%'  
      
     OPEN Cursor_DELETE  
         FETCH NEXT FROM Cursor_DELETE INTO @Table_Schema,@Name_Of_Table  
         PRINT 'We are inside the corsur, get:' + @Name_Of_Table  
      
         WHILE @@FETCH_STATUS = 0 BEGIN  
             SET @Delete_STR=N'  
                 ;WITH CTE AS(  
                     SELECT *,DENSE_RANK()OVER(ORDER BY Load_Skey DESC) RNum  
                     FROM '+QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Name_Of_Table)+'  
                 )  
                 DELETE FROM CTE   
                 WHERE RNum > '+@Top+''  
             PRINT @Delete_STR  
             --EXEC (@Delete_STR)  
             FETCH NEXT FROM Cursor_DELETE INTO @Table_Schema,@Name_Of_Table  
         END  
      
     CLOSE Cursor_DELETE  
     DEALLOCATE Cursor_DELETE  
 END  
  

Answer 2

There is no issue with your code. You will need to provide full information to reprodcuce your scenarion so we will see what is micssing in your case

Here is a full example using your code - I print the query instead of execute the delete directly so I can check it. Do the same in your case and execute manually all the queries which were printed

Not! next time please format the code better using the editor "code" option

use tempdb  
GO  
  
CREATE TABLE testing_Emp_tmp_01 (Load_Skey int)  
GO  
INSERT testing_Emp_tmp_01(Load_Skey)values (1),(4),(5),(3)  
GO  
CREATE TABLE testing_Emp_tmp_02 (Load_Skey int)  
GO  
INSERT testing_Emp_tmp_02(Load_Skey)values (1),(4),(44),(34),(76),(23)  
GO  
  
CREATE OR ALTER PROCEDURE SP_TEST_DELETE  
	@Top VARCHAR(10) = '3'  
AS BEGIN  
	DECLARE @Name_Of_Table VARCHAR(200)  
	DECLARE @Delete_STR VARCHAR(MAX)  
  
	DECLARE Cursor_DELETE CURSOR FOR  
		SELECT t.name AS table_name   
		FROM sys.tables t  
		WHERE schema_name(t.schema_id) = 'DBO' AND t.name like '%Emp_tmp%'  
  
	OPEN Cursor_DELETE  
		FETCH NEXT FROM Cursor_DELETE INTO @Name_Of_Table  
		PRINT 'We are inside the corsur, get:' + @Name_Of_Table  
  
		WHILE @@FETCH_STATUS = 0 BEGIN  
			SET @Delete_STR=N'  
				;WITH CTE AS(  
					SELECT *,DENSE_RANK()OVER(ORDER BY Load_Skey DESC) RNum  
					FROM '+@Name_Of_Table+'  
				)  
				DELETE FROM CTE   
				WHERE RNum > '+@Top+''  
			PRINT @Delete_STR  
			--EXEC (@Delete_STR)  
			FETCH NEXT FROM Cursor_DELETE INTO @Name_Of_Table  
		END  
  
	CLOSE Cursor_DELETE  
	DEALLOCATE Cursor_DELETE  
END  
GO  
  
EXECUTE SP_TEST_DELETE  
GO