Issue in dynamic sql for 1 condition

Question

I asked this question yesterday also but I think there was something missing in the question so I am posting it again.

Here is my SP which I created:

 ALTER PROCEDURE [dbo].[USP_Insert__DDL_Records_Into_FinalTable]
 AS 
 BEGIN

 DECLARE @DBName VARCHAR(50)
 DECLARE @SQLTableDet VARCHAR(MAX)
 DECLARE @SQLInsDet VARCHAR(MAX)
 DECLARE @DelSQLTableDet VARCHAR(MAX)
 DECLARE @Table_Name VARCHAR(20)
 DECLARE @DestDB VARCHAR(20)

 SET @DestDB = 'DB_Audit'
 SET @Table_Name ='t_ddl_log'

 DECLARE Database_Details CURSOR FOR 
 SELECT name   
 FROM 
 sys.databases 
 WHERE
 database_id  =15       
 OPEN Database_Details

 FETCH NEXT FROM Database_Details INTO @DBName

 WHILE @@FETCH_STATUS = 0
 BEGIN

IF (OBJECT_ID(+ @DBName + '.dbo.' + @Table_Name) IS NOT NULL) 

AND ('SELECT CAST (COUNT(*) AS varchar(20)) FROM ' + @DBName + '.dbo.' + @Table_Name) > '0'
BEGIN
    SET @SQLTableDet = ' INSERT INTO '+ @DestDB + '.dbo.'+ @Table_Name + 
                       ' SELECT * FROM ' + @DBName + '.dbo.' + @Table_Name   

    PRINT @SQLTableDet

    SET @SQLInsDet  = 'USE [' + @DBName + ']' + @SQLTableDet
    PRINT @SQLInsDet
    EXEC (@SQLInsDet)

    SET @DelSQLTableDet = 'DELETE FROM ' +  @DBName + '.dbo.' + @Table_Name 
    PRINT @DelSQLTableDet
    EXEC (@DelSQLTableDet)  
END     


 FETCH NEXT FROM Database_Details INTO @DBName
 PRINT @DBName

 END  
CLOSE Database_Details  
DEALLOCATE Database_Details
END

**Output:** <br>

INSERT INTO DB_Audit.dbo.t_ddl_log SELECT * FROM busstarget.dbo.t_ddl_log
USE [busstarget] INSERT INTO DB_Audit.dbo.t_ddl_log SELECT * FROM  busstarget.dbo.t_ddl_log

(0 row(s) affected)
DELETE FROM busstarget.dbo.t_ddl_log

(0 row(s) affected)
Busstarget

Issue:

IF (OBJECT_ID(+ @DBName + '.dbo.' + @Table_Name) IS NOT NULL)   
AND ('SELECT CAST (COUNT(*) AS varchar(20)) FROM ' + @DBName + '.dbo.' +  @Table_Name) > '0'

I am trying to figured out how to count the number of rows dynamically wherein if its > 0 only then it should go inside the loop. And in my above code my AND statement is somehow failing or maybe something is wrong.

Answer 1

you can also check for null if count is 0 like this:

IF ((OBJECT_ID(+ @DBName + '.dbo.' + @Table_Name) IS NOT NULL)   
AND ('SELECT nullif(COUNT(*),0) FROM ' + @DBName + '.dbo.' +  @Table_Name) is not null)

Answer 2

One will require to find out the count dynamically also the if condition too would be in dynamic query.

I have added another stored procedure to check the Count and Status of the table. You can have this implementation in the original stored procedure itself.

Helping Stored Procedure

Create proc [dbo].[prc_CheckStatus]
@DBName varchar(100),
@TableName  varchar(100)
as
Begin
   Create Table #userData  
   (
       RecordCount int,Remark varchar(100)
   )
   Declare @Qquery varchar(1000)
   SET @Qquery ='insert into #userData
   Select count(*),''RecordCount'' from '+@DBName+'.dbo.'+ @TableName
   -- Print @Qquery 
   Exec(@Qquery )
   SET @Qquery ='
     insert into #userData
     Select count(*),''TableExistance'' from '+@DBName+'.information_schema.columns where TABLE_CATALOG='''+ @DBName+'''
     and TABLE_NAME='''+@TableName+''''

   --Print @Qquery 
   Exec(@Qquery )
   Select * from #userData
END

Implementation in your Stored Procedure.

    ALTER PROCEDURE [dbo].[USP_Insert__DDL_Records_Into_FinalTable]
 AS 
 BEGIN

  Create Table #userData  
   (
       RecordCount int,Remark varchar(100)
   )  
 DECLARE @DBName VARCHAR(50)
 DECLARE @SQLTableDet VARCHAR(MAX)
 DECLARE @SQLInsDet VARCHAR(MAX)
 DECLARE @DelSQLTableDet VARCHAR(MAX)
 DECLARE @Table_Name VARCHAR(200)
 DECLARE @DestDB VARCHAR(20)

 SET @DestDB = 'DB_Audit'
 SET @Table_Name ='t_ddl_log'

 DECLARE Database_Details CURSOR FOR 
 SELECT name   
 FROM 
 sys.databases 
 WHERE
 database_id  =15              
 OPEN Database_Details

 FETCH NEXT FROM Database_Details INTO @DBName

 WHILE @@FETCH_STATUS = 0
 BEGIN

     insert into #userData
     exec prc_CheckStatus @DBName,@Table_Name


       IF (Select count(*) from #userData) > '0'
       BEGIN
           SET @SQLTableDet = ' INSERT INTO '+ @DestDB + '.dbo.'+ @Table_Name + 
                              ' SELECT * FROM ' + @DBName + '.dbo.' + @Table_Name   

           PRINT @SQLTableDet

           SET @SQLInsDet  = 'USE [' + @DBName + ']' + @SQLTableDet
           PRINT @SQLInsDet
           EXEC (@SQLInsDet)

           SET @DelSQLTableDet = 'DELETE FROM ' +  @DBName + '.dbo.' + @Table_Name 
           PRINT @DelSQLTableDet
           EXEC (@DelSQLTableDet)  
       END     

       Truncate table #userData
 FETCH NEXT FROM Database_Details INTO @DBName
 PRINT @DBName

 END  
CLOSE Database_Details  
DEALLOCATE Database_Details
END

I have used Temporary Table to store the result of Dynamic Query.