Removing empty resultsets from looping through multiple tables at once-CodePudding

My goal is to remove empty resultsets from a script I wrote to find matching values within multiple tables at once. I am currently returning empty resultsets where I've explicitly written to remove any row where the AssociatedId is empty. How can I write this to remove the empty resultsets?

Here is my SQL:

Select * From #Agreement

Declare @TableName nvarchar(256) 
Set @TableName = ''
While @TableName IS NOT NULL
Begin
    Set @TableName =  (
    Select top 1 MIN(QUOTENAME(TABLE_SCHEMA)   '.'   QUOTENAME(TABLE_NAME))
    From   INFORMATION_SCHEMA.TABLES
    Where  TABLE_TYPE = 'BASE TABLE'
        AND QUOTENAME(TABLE_SCHEMA)   '.'   QUOTENAME(TABLE_NAME) > @TableName
        AND TABLE_NAME LIKE '%Association%'
        AND QUOTENAME(TABLE_NAME) NOT IN ('[AssociationPrivilege]'
                                        , '[AssociationMultiplicityAssociation]'
                                        , '[AssociationPrivilegeAssociation]'
                                        , '[AssociationTypeAssociation]'
                                        , '[AssociationType]'
                                        , '[AsyncOperationResultsAssociation]'
                                        , '[AttributeGroupRoleMappingAssociation]'
                                        , '[EntityAttributeDataTypeAssociation]'
                                        , '[AutoDelegationAssociation]'
                                        , '[EntityAssociation]'
                                        , '[AssociationMultiplicity]'
                                        , '[RoleAssociationMapping]')
        Group BY TABLE_SCHEMA, TABLE_NAME
    )

    Declare @SQLQuery nvarchar(500)
    Set @SQLQuery = 'SELECT AssociationName, AssociatedId FROM '   @TableName   ' WHERE AssociatedId 
        IN (Select AgreementSysID From #Agreement) AND CAST(AssociatedId AS NVARCHAR(255)) NOT IN ('''') 
        GROUP BY AssociationName, AssociatedId'
    Exec sp_executesql @SqlQuery
End
GO

CodePudding user response：

Create a second query to see how many records are going to be returned, and only run the select if there are results available.

    Declare @SQLQueryCount nvarchar(max), @SQLQueryResults nvarchar(max), @RecordCount int = 0, @WhereClause nvarchar(max);

    Set @WhereClause = ' WHERE AssociatedId 
        IN (Select AgreementSysID From #Agreement) AND CAST(AssociatedId AS NVARCHAR(255)) NOT IN ('''') 
        GROUP BY AssociationName, AssociatedId';

    Set @SQLQueryCount = 'SELECT @RecordCount = count(*) FROM '   @TableName   @WhereClause;
    Set @SQLQueryResults = 'SELECT AssociationName, AssociatedId FROM '   @TableName   @WhereClause;

    Exec sp_executesql @SqlQueryCount, N'@RecordCount int out', @RecordCount out;

    If @RecordCount > 0 Begin
        Exec sp_executesql @SqlQueryResult;
    End;

Note: For dynamic SQL I recommend always using nvarchar(max) because adding a length could cause very undesired results if you modified the query to make it longer than you can store.