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

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.