Check if table is empty in a dynamic code - SSMS V18

Question

Hey guys !!

I am trying to check if a table exists. Here is my code:

DECLARE @sql NVARCHAR(500)  
DECLARE @date VARCHAR(8)  
DECLARE @table VARCHAR(100)  
DECLARE @schema VARCHAR(60)  
  
SET @date = '20221106'  
SET @schema = 'SurveyInterface'  
SET @table = 'tblSIRSessionsReport_' + @date  
SET @sql = 'IF(NOT EXISTS(SELECT 1 FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ')) BEGIN PRINT ''hey'' END;'  
PRINT @sql  
EXEC msdb.sys.sp_executesql @sql  

I get the following error message :
Msg 208, Level 16, State 1, Line 1
Invalid object name 'SurveyInterface.tblSIRSessionsReport_20221106'.

The table does exists !!

If I execute the output of the print statement in the above code, it's working.

IF(NOT EXISTS(SELECT 1 FROM [SurveyInterface].[tblSIRSessionsReport_20221106])) BEGIN PRINT 'hey' END;  

What's wronf with this !!

Thanks in advance for your help.
Mylene

Answer 1

EXEC msdb.sys.sp_executesql @alenzi

Since you have database-qualified sp_executesql, the query runs in the context of the specified database (msdb). Execute the proc without qualification to run the in current database context:

EXEC sp_executesql @sql;  

Answer 2

Hi @Chalut, Mylène - ITSLM/GCSTI ,

Please check [SurveyInterface]. [tblSIRSessionsReport_20221106] is in (msdb), if not, then do what Dan did.

Best regards
Niko