Conditional Joins - Dynamic SQL

Question

The DBA here at work is trying to turn my straightforward stored procs into a dynamic sql monstrosity. Admittedly, my stored procedure might not be as fast as they'd like, but I can't help but believe there's an adequate way to do what is basically a conditional join.

Here's an example of my stored proc:

SELECT 
*
FROM
table
WHERE
(
    @Filter IS NULL OR table.FilterField IN 
    (SELECT Value FROM dbo.udfGetTableFromStringList(@Filter, ','))
)

The UDF turns a comma delimited list of filters (for example, bank names) into a table.

Obviously, having the filter condition in the where clause isn't ideal. Any suggestions of a better way to conditionally join based on a stored proc parameter are welcome. Outside of that, does anyone have any suggestions for or against the dynamic sql approach?

Thanks

Answer 1

Take a look here Dynamic Search Conditions in T-SQL

Answer 2

You could INNER JOIN on the table returned from the UDF instead of using it in an IN clause

Your UDF might be something like

CREATE FUNCTION [dbo].[csl_to_table] (@list varchar(8000) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
    DECLARE  @index INT,
      @start_index INT,
      @id INT

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

     IF SUBSTRING(@list,@index,1) = ','
     BEGIN

      SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
      INSERT @list_table ([id]) VALUES (@id)
      SELECT @start_index = @index + 1
     END
     SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
    INSERT @list_table ([id]) VALUES (@id)
    RETURN
END

and then INNER JOIN on the ids in the returned table. This UDF assumes that you're passing in INTs in your comma separated list

EDIT:

In order to handle a null or no value being passed in for @filter, the most straightforward way that I can see would be to execute a different query within the sproc based on the @filter value. I'm not certain how this affects the cached execution plan (will update if someone can confirm) or if the end result would be faster than your original sproc, I think that the answer here would lie in testing.

Answer 3

Looks like the rewrite of the code is being addressed in another answer, but a good argument against dynamic SQL in a stored procedure is that it breaks the ownership chain.

That is, when you call a stored procedure normally, it executes under the permissions of the stored procedure owner EXCEPT when executing dynamic SQL with the execute command,for the context of the dynamic SQL it reverts back to the permissions of the caller, which may be undesirable depending on your security model.

In the end, you are probably better off compromising and rewriting it to address the concerns of the DBA while avoiding dynamic SQL.

Answer 4

I am not sure I understand your aversion to dynamic SQL. Perhaps it is that your UDF has nicely abstracted away some of the messyness of the problem, and you feel dynamic SQL will bring that back. Well, consider that most if not all DAL or ORM tools will rely extensively on dynamic SQL, and I think your problem could be restated as "how can I nicely abstract away the messyness of dynamic SQL".

For my part, dynamic SQL gives me exactly the query I want, and subsequently the performance and behavior I am looking for.

Answer 5

I don't see anything wrong with your approach. Rewriting it to use dynamic SQL to execute two different queries based on whether @Filter is null seems silly to me, honestly.

The only potential downside I can see of what you have is that it could cause some difficulty in determining a good execution plan. But if the performance is good enough as it is, there's no reason to change it.

Answer 6

No matter what you do (and the answers here all have good points), be sure to compare the performance and execution plans of each option.

Sometimes, hand optimization is simply pointless if it impacts your code maintainability and really produces no difference in how the code executes.

I would first simply look at changing the IN to a simple LEFT JOIN with NULL check (this doesn't get rid of your udf, but it should only get called once):

SELECT *
FROM table
LEFT JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value
WHERE @Filter IS NULL
    OR filter.Value IS NOT NULL

Answer 7

It appears that you are trying to write a a single query to deal with two scenarios:
1. @filter = "x,y,z"
2. @filter IS NULL

To optimise scenario 2, I would INNER JOIN on the UDF, rather than use an IN clause...

SELECT * FROM table
INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
  ON table.FilterField = filter.Value

To optimise for scenario 2, I would NOT try to adapt the existing query, instead I would deliberately keep those cases separate, either an IF statement or a UNION and simulate the IF with a WHERE clause...

TSQL IF

IF (@filter IS NULL)
  SELECT * FROM table
ELSE
  SELECT * FROM table
  INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value

UNION to Simulate IF

SELECT * FROM table
  INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value

UNION ALL

SELECT * FROM table WHERE @filter IS NULL

The advantage of such designs is that each case is simple, and determining which is simple is it self simple. Combining the two into a single query, however, leads to compromises such as LEFT JOINs and so introduces significant performance loss to each.