T-SQL - Conditional WHERE clause

Dont have enough points to add a comment for more questions. If possible simplify your code by doing something like this as you seem to know what the value is for each item.

CREATE TYPE [dbo].[Param] AS TABLE ( [FlagVal] NVARCHAR(100),NumValue int  )
)
GO

DECLARE @Param [dbo].[Param];
INSERT  INTO @Param ( [FlagVal] )
VALUES    ( 'Yes',10 )
        , ( 'NO',100 )
        , ( 'Maybe_JKL',### )
        , ( 'Maybe_XYZ',### )
        --, ( 'Maybe_PQR' )
        ;

Adding the numeric value will simplify the code. And them maybe you can just build the where clause from it. Could even add missing_Y_and_N and insert the value.

Don't use dynamic SQL for this,Declare your @DataT table normally and proceed

Create a #temp table from @DataT(the actual data table) to make rows based on columns

if object_id('tempdb..#temp') is not null drop table #temp
select distinct [Flag],case when isnull(FValY,'') <> '' then 'Yes' 
when isnull(fvaln,'')<>'' then 'NO'
when isnull(fvalmb,'') <> '' then 'Maybe_' end As FlagVal into #temp 
from @DataT where isnull(Flag,'') <>''

Join the #temp table with @param and @DataT

select distinct c.* from @Param a inner join #temp b 
on a.[FlagVal] like b.FlagVal + '%' 
inner join @DataT c on b.Flag = c.Flag

You will get the desired output

I made your table permanent for this example just because it was simpler and easier to factor that piece out. Shouldn't make a difference in the guts of the candidate solution. Like another poster, there are several requirements questions that I couldn't quite answer for myself, but don't have the points to inquire about. Specifically, I notice that your sample data has no rows with values in two or more of the FVal* columns. Therefore I did not test for / accommodate that (yet).

/* BEGIN One-time structure setup */
CREATE TYPE [dbo].[Param] AS TABLE ( [FlagVal] NVARCHAR(100) )
GO


CREATE TABLE [dbo].[DataT](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Flag] [int] NULL,
    [FValY] [nvarchar](45) NULL,
    [FValN] [nvarchar](45) NULL,
    [FValMB] [nvarchar](45) NULL,
 CONSTRAINT [PK_DataT] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/* END One-time structure setup */

/* BEGIN test data setup */
INSERT  INTO DataT ( [Flag], [FValY], [FValN], [FValMB] )
VALUES    ( 10, 'XYZ', NULL, NULL )
        , ( 10, 'ABC', NULL, NULL )
        , ( 10, 'XYZ', NULL, NULL )
        , ( 100, NULL, 'MNO', NULL )
        , ( 100, NULL, 'STU', NULL )
        , ( 1000, NULL, NULL, 'Maybe_JKL' )
        , ( 1000, NULL, NULL, 'Maybe_XYZ' )
        , ( 1000, NULL, NULL, 'Maybe_PQR' )
        , ( NULL, NULL, NULL, NULL )
        , ( NULL, NULL, NULL, NULL )
;
/* END test data setup */

/* BEGIN test param */
DECLARE @Param Param;

INSERT  INTO @Param ( [FlagVal] )
VALUES    ( 'Yes' )
        ,( 'NO' )
        , ( 'Maybe_JKL' )
        , ( 'Maybe_XYZ' )
        , ( 'Maybe_PQR' )
        ;
/* END test param */


/* BEGIN query */
DECLARE @ParamHasYes bit;
SET @ParamHasYes = 0;

DECLARE @ParamHasNo bit;
SET @ParamHasNo = 0;

IF EXISTS (SELECT FlagVal FROM @Param WHERE LOWER(FlagVal) = 'yes') SET @ParamHasYes = 1;
IF EXISTS (SELECT FlagVal FROM @Param WHERE LOWER(FlagVal) = 'no') SET @ParamHasNo = 1;

WITH DataView AS 
(
    SELECT 
        Flag
        ,FValY
        ,FValN
        ,FValMB
        /* IMPORTANT - non-matches for Y and N columns below must be set to NULL (NOT 0!) 
        so that rows are excluded when the param list is empty 
        i.e., @ParamHasYes = 0 and/or @ParamHasNo = 0 */
        ,CASE WHEN FValY IS NOT NULL THEN 1 ELSE NULL END AS RowHasYes 
        ,CASE WHEN FValN IS NOT NULL THEN 1 ELSE NULL END AS RowHasNo
    FROM
        DataT
)
-- Get all the rows that have a value for the yes column
SELECT 
    DataView.*
FROM 
    DataView
WHERE
    RowHasNo = @ParamHasNo
UNION ALL
-- Get all the rows that have a value for the no column
SELECT 
    DataView.*
FROM 
    DataView
WHERE
    RowHasYes = @ParamHasYes
UNION ALL
-- Get all the rows that match the MB list
SELECT 
    DataView.* 
FROM 
    DataView
    INNER JOIN @Param P ON DataView.FValMB = P.FlagVal
ORDER BY
    Flag
    ,FValY
    ,FValN
    ,FValMB
;
/* END query */