Sql-server – SQL Conditional Join – remove duplication from Stored Procedure

ctejoin;sql server

This stored procedure accepts a search parameter. I want to join on a table valued function if the search parameter is not null. The way I have written it works, but it seems like lots of duplication of code.

Is there a better way of writing this query to avoid the check for the parameter? Something like a conditional join?

–this is actually passed in to the stored procedure as a parameter which may be NULL (no search text just filters on page)
DECLARE @Search varchar(100)

IF @Search IS NULL

WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO, 0
    FROM IMF AS I
)  
SELECT FIM_NO, RNK
    FROM Catalog_Search_Results
    ORDER BY rnk desc

ELSE

WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO,funcItemSearch.RNK
    FROM IMF AS I 
    INNER JOIN dbo.Catalog_ItemSearch(@Search) funcItemSearch ON I.FIM_NO = funcItemSearch.FIM_NO
)  
SELECT FIM_NO, Rnk
    FROM Catalog_Search_Results
    ORDER BY Rnk desc;

Best Answer

Your code looks ok. Conditional join would lead you to parameter sniffing issue and you used one of two most popular solutions to avoid it. Also you may want to use the second approach - dynamic sql, something like this:

DECLARE @sql NVARCHAR(max);

SET @sql = N'
WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO, 0
    FROM IMF AS I
    '
IF @Search IS NOT NULL
   SET @sql = CONCAT(@sql, 'INNER JOIN dbo.Catalog_ItemSearch(@Search) funcItemSearch ON I.FIM_NO = funcItemSearch.FIM_NO');

SET @sql = CONCAT(@sql, ')  
SELECT FIM_NO, Rnk
    FROM Catalog_Search_Results
    ORDER BY Rnk desc')                 

EXEC sp_executesql @sql, N'@Search varchar(100)', @Search
Related Question