SQL Server + Conditional join

Question

Is there a way which I can perform a conditional join something like this:

   CREATE TABLE #Entity 
   (    
    [AutoID] [int],
    [Code] [nvarchar](50) NOT NULL,
   )

    INSERT #Entity
    EXEC Entity_GetEntitiesByUserId @UserID

    DECLARE @Condition bit = 0

    SELECT * FROM [Stuff] s

       IF @Condition = 1 BEGIN

       INNER JOIN 
       (
        SELECT Code as eCode from
        #Entity
       ) e
        ON E.eCode  = s.EntityCode 

       END

       WHERE DeletedBy IS NULL

Thanks.

Answer 1

I know this is a very old question, but I have a different/simpler solution that works well for me.

Only join on TableC if you supply the param @tableCid

        declare @tableCid int = null;
        Select a.Id, b.AId, -- whatever other columns
        from TableA a
        inner join TableB b on b.AId = a.Id 
        left join TableC c on c.Id = b.CId 
    where
        1= case 
            when @tableCid is not null and c.Id = @tableCid then 1 
            when @tableCid is null then 1
        end
        

Answer 2

This will do logically what you want:

SELECT * 
FROM [Stuff] s
WHERE 
 DeletedBy IS NULL 
 and (@Condition = 0 or s.EntityCode in (select E.code from #Entitye))