Retrieve data using LEFT INTERACTION when Condition false otherwise INNER JOIN

Question

In proc, I need to get data based on a condition.

If this condition is false, I need to use the LEFT OUTER JOIN, but if this condition is true, I need to use the INNER JOIN.

This is what I tried.

ALTER PROCEDURE [dbo].[GetFooBarData]    
@UDT VarcharUDT readonly
AS BEGIN    

DECLARE @IsUDTNull BIT
SELECT @IsUDTNull = ISNULL((SELECT TOP 1 1 FROM @UDT),0)

SELECT [Foo], 
       [Bar]
  FROM [dbo].[FooBar] FB
  LEFT OUTER JOIN @UDT T ON @IsUDTNull = 0 OR FB.Foo LIKE '%' + T.Item + '%'
END

I call proc like this

DECLARE @sa VarcharUDT

INSERT INTO @sa (Item)
SELECT 'XS0995537155'

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetFooBarData]
        @UDT = @sa

SELECT  'Return Value' = @return_value

GO

This does not work properly and returns all data, even if the UDT contains a value.

UDT

CREATE TYPE [dbo].[VarcharUDT] AS TABLE(
    [Item] [varchar](100) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [Item] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Answer 1

If you want to do this in one of the statements, use LEFT JOINand check the condition in the sentence WHERE:

SELECT [Foo], [Bar]
FROM [dbo].[FooBar] FB LEFT OUTER JOIN
      @UDT T 
      ON FB.Foo LIKE '%' + T.Item + '%'
WHERE (@UseLeftJoin = 1) OR (T.Item IS NOT NULL);