I have a main table with another 7 tables attached to it by a key. I have 10 different filter types in the secondary tables I will not always have all types of filters in one QUERY, some of them may be equal to NULL. The problem is that the secondary tables are very large and if they do not need to be accessed by the filter then it is best not to include them in the INNER JOIN, How to make it the most effective?

  • 1
    Please edit your question and add more details.Describe the environment in which the queries are executed. Is it a web app, ad-hoc SSMS query, reporting tool? Including a set of sample queries and table structures with some test data would be a good start. As about efficiency, what kind of performance you are looking at? N transactions/second, response within n milliseconds, something else?
    – vonPryz
    Commented Dec 30, 2018 at 8:46
  • Question is so general without any example and data yet it is upvoted ?
    – KumarHarsh
    Commented Dec 31, 2018 at 5:32

1 Answer 1


You can use dynamic SQL to generate a query based on varied requirements. This way, you can exclude joins if you don't need them, like so:

    SET @sql = 'SELECT * FROM myTable AS t ';
IF @param1 IS NOT NULL
    SET @sql = @sql + 'JOIN mySecondTable AS s ON s.Id = t.mySecondTableId ';
IF @param2 IS NOT NULL
    SET @sql = @sql + 'JOIN myThirdTable AS x ON x.Id = t.myThirdTableId';
-- repeat as required to build a valid query

DECLARE @params NVARCHAR(MAX) = '@filterParam1 INT, @filterParam2 NVARCHAR(128);'

IF @filterParam1 IS NOT NULL
   SET @filters = '[column] = @filterParam1';

IF @filterParam2 IS NOT NULL
  SET @filters = CASE WHEN @filters IS NULL 
                      THEN '' 
                      ELSE ',' 
                  END + '[column2] = @filterParam2';

IF @filters IS NOT NULL
   SET @where = 'WHERE ' + @filters;
   SET @sql = @sql + @where;

EXEC sp_ExecuteSql @stmt = @sql
                 , @params = @params
                 , @filterParam1 = @filterParam1
                 , @filterParam2 = @filterParam2;

Assuming that @param1 is null and @filterParam1 is also null, it would generate the following query:

  FROM myTable AS t
  JOIN myThirdTable AS x on x.Id = t.myThirdTableId
 WHERE [column2] = @filterParam2;

Notice how the join to mySecondTable has not been included and the unnecessary filter has not been included either. You can find more examples of how to use sp_executeSql on Microsoft Docs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.