Dynamic SQL - many choices but how best to order it?

Transact-SQL
1

Unfortunately I am in a situation where we need to generate dynamic SQL to provide results based on parameters passed into either the SPROC of the code itself to take into consideration.

Imagine this:

6 drop down lists.

Each list somewhat cascades but they work independently. For instance:

  • Brand
  • Size
  • Pattern
  • Ply
  • Compounds
  • TRA

These are SEPERATE drop down lists. Each one has a list of items with ID's.
When selected, they need to perform a query and dynamically join into their respective tables to bring back results.

They need to bring back results for the drop down items for those which are NOT selected. The catch is that ANY ONE of the lists could be selected with ANY OTHER combination (or on its own completely) i.e:

Brand
OR
Brand AND TRA
OR
TRA AND Pattern AND Ply
OR
Ply AND Compound AND Brand

etc...

Then for the ones which are not chosen (the drop down list values), I have to execute IT's query using the values chosen from above (i.e if you select brand and TRA, i need to query for ply, compound, TRA etc...)
what is the best way of tackling this type of problem? How can I easily (at most?) Generate the dynamic SQL without figuring out all the possibilities in a case by case basis which will take a VERY long time.

thank you.

2

Join main table with left join of these brand, size, pattern tables. So that if you have given value that will be filtered. Otherwise it will return null value.

3

Without using Dynamic SQL you could do

WHERE
        (@Brand IS NULL OR MyBrandColumn = @Brand)
    AND (@Size IS NULL OR MySizeColumn = @Size)
... etc.

but it tends to make for an inefficient query, particularly if typically several of the @Parameters are NULL (you can use BLANK STRING instead of NULL or "*" or whatever your "Anything" value is. Note that IF you do that you cannot then match THAT value :slight_smile: )

Alternative is Dynamic Parametrised SQL which will perform very well. If done right SQL will cache the query plan for each variation of the actual query, so any query that is popular (e..g if Size & Brand is the most commonly used query) will remain in the query plan cache.

DECLARE @SQLWhere nvarchar(MAX) = '1=1', @SQL nvarchar(MAX)

IF @Brand IS NOT NULL
BEGIN
        SELECT @SQLWhere = @SQLWhere
                         + ' AND MyBrandColumn = @Brand'
END

IF @Size IS NOT NULL
BEGIN
        SELECT @SQLWhere = @SQLWhere
                         + ' AND MySizeColumn = @Size'
END

... etc ...

SELECT @SQL = 'SELECT Col1, Col2, ... FROM MyTable WHERE '
            + @SQLWhere
            + ' ORDER BY '
            + CASE @SortOrder
                  WHEN 1 THEN 'MyBrandColumn'
                  WHEN 2 THEN 'MySizeColumn'
                  ELSE ''
                  END
            + ', MyPKeyTieBreakColumn'

-- DEBUG:
PRINT 'SQL=' + @SQL

EXEC sp_ExecuteSQL @SQL
      , '@Brand varchar(10), @Size int'
      , @Brand, @Size

Note that sp_ExecuteSQL takes a definition for the parameters and then a list of the parameters and that it does not matter whether those parameters are used, or not, so you can always include the complete list of @parameters but the @SQL may only reference some/none of them.

There are permission issues associated with using Dynamic SQL, in particular if you normally code everything in SProcs and ONLY give EXECUTE permissions to your users, rather than SELECT permission on all tables :slight_smile:, then you need special handling of this in SQL to continue to only give EXECUTE permission to the users. It can be done though ... its just a bit more hassle.

Watch out for "SQL Injection" when you do dynamic SQL, which is why I would recommend using SP_EexcuteSQL rather than just constructing some fully-formed SQL and using EXEC to run it - because EXEC is open to SQL Injection, and will have much worse performance than sp_ExecuteSQL