SQL Join Table on either matching row or all if second table is empty

I am new to SQL and need some assistance on a join between two tables in a stored procedure. The stored procedure takes as input multiple comma separated strings, one for each column the user wants to search the main table for. Then, I declare a table to store the separated string values from the users search. I need to return results that the main table and the different search string table have in common, or all rows from the main table if the search table is empty. (Many rows in the main table have null values)

Here is what I have for the creation of the search string table (@testtype and @location represent the users inputted search strings):

    DECLARE @tempTestType AS TABLE( 
              TestType NVARCHAR(MAX) );
    IF @testtype IS NOT NULL
    BEGIN
       INSERT INTO @tempTestType SELECT * FROM dbo.CLR_SplitStrings( @testtype, ',' );
    END


    DECLARE @tempLocation AS TABLE( 
              fkLocation NVARCHAR(MAX) );
    IF @location iS NOT NULL
    BEGIN
       INSERT INTO @tempLocation SELECT * FROM dbo.CLR_SplitStrings( @location, ',' );
    END

Then this is what I have for joining those tables to the main table:

INSERT INTO @answerTable 
SELECT *
from MainTable as T
     join @tempTestType as tTT on (tTT.TestType = T.fkTestType)
     join @tempLocation as tL on (tL.fkLocation = T.fkLocation)
WHERE 1=1 
AND (ISNULL(@notes, '')='' OR T.nkNotes LIKE '%' + @notes + '%')

OPTION(RECOMPILE);

I have looked into the different types of joins, and tried join, inner join, and left join but none of them work. The main table as over 3 million records, so it has been difficult to find a solution that executes in a reasonable time.

I tried left join because I thought that if the user searched by a column, the correct search values would be stored in the temporary table to be joined to the main. If the user did not want to search by a certain column, nothing would be added to the temporary table and all rows from that column would be returned. Instead, for left join the incorrect rows are returned, and way more rows than expected appear in the main table, making the procedure take 2 minutes to execute.

For inner join/join, there are no rows returned and the main table just appears empty.

Does anyone know what I am doing wrong or what I should try instead?

EDIT: @notes is a one input string that matches a note column in the main table. It is a different search option that does not need a separate search table created for it

Answer

If I understand you correctly, you need to insert values with both criterias if they’re specified or all the rows with one criteria if the second one is not specified. Try this code:

INSERT INTO @answerTable 
SELECT T.*
from MainTable as T
WHERE 
(@notes is null OR T.nkNotes LIKE '%' + @notes + '%') and
(
(T.fkTestType in (select TestType from @tempTestType) and T.fkLocation in (select fkLocation from @tempLocation))
or 
((select count(1) where exists (select TestType from @tempTestType))=0 and T.fkLocation in (select fkLocation from @tempLocation))
or
(T.fkTestType in (select TestType from @tempTestType) and (select count(1) where exists (select fkLocation from @tempLocation))=0)
)
OPTION(RECOMPILE);

Advertisement

Answer