How can I add "in" keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012 I want the parameters in Age and Gender to accept more than one value

    Create Procedure [Admission].[SelectPlayerData]

@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish varchar(70),
@Age varchar(200),
@Gender varchar(100),
@Mobile1 varchar(20),
@Mobile2 varchar(20)
as
set nocount on
begin
 Declare @BaseQuery nvarchar(max) 
='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish, 
                         Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
FROM            Evaluation.Players INNER JOIN
                         Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
    , @ParamList nvarchar(max) = N'@p1 varchar(20), @p2 varchar(70), @p3 varchar(70),@p4 varchar(200), @p5 varchar(100), @p6 varchar(20) , @p7 varchar(20)'
    , @WhereClause nvarchar(max) = ' WHERE 1=1';

IF @MembershipNumber IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = @p1';
    END


IF @PlayerName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = @p2';
    END


IF @PlayerNameEnglish IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = @p3';
    END


IF @Age IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in (@p4)';
    END


IF @Gender IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in (@p5)';
    END


    IF @Mobile1 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = @p6';
    END


    IF @Mobile2 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = @p7';
    END

    SET @BaseQuery = @BaseQuery + @WhereClause;


    EXECUTE sp_executesql @BaseQuery, @ParamList,
     @p1 = @MembershipNumber,
     @p2 = @PlayerName,
     @p3 = @PlayerNameEnglish,
     @p4 = @Age,
     @p5 = @Gender,
     @p6 = @Mobile1,
     @p7 = @Mobile2;

end         


the following execution does not work properly

USE [SportsActivities]
GO

DECLARE @return_value int

EXEC    @return_value = [Admission].[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = N'12,14',
        @Gender = N'Male,Female',
        @Mobile1 = NULL,
        @Mobile2 = NULL

SELECT  'Return Value' = @return_value

GO
I think that you need to split up the male + female values, so you can add the ' and the comma's characters for each gender. If you are on sql 2016 you could use string split:

Example for SQL Server 2016 and up:

DECLARE @gender nvarchar(255)= N'Male,Female';
DECLARE @genderlist nvarchar(1000);
SET @genderlist=(select ''''+ value+''',' from string_split(@Gender,',')
FOR XML PATH(''));
select @genderlist = left(@genderlist,len(@genderlist)-1);
select @genderlist;


SQL Server 2012 Example

declare @gender nvarchar(255)= N'Male,Female'
declare @genderlist nvarchar(1000)
declare @genderlist2 table (genders nvarchar(1000))
declare @temp int
INSERT INTO @genderlist2(genders)
SELECT Split.a.value('.', 'NVARCHAR(MAX)')  as a

FROM
(
    SELECT CAST('&lt;X&gt;'+REPLACE(@Gender, ',', '&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X')
 AS Split(a)

 set @genderlist=(
select ''''+genders+''',' from @genderlist2
for xml path(''))
select @genderlist = left(@genderlist,len(@genderlist)-1);
select @genderlist


Source

Another issue apart from splitting your string: 

One other issue found, is that you need to keep the variables out of the where string created, otherwise the variable will not change to what it is holding. E.G. @p4 would have stayed @p4 instead of parsing to 12,14. I changed it to @Age in my example.

SQL Server 2012 Full procedure 

   ALTER Procedure [Evaluation].[SelectPlayerData]

    @MembershipNumber varchar(20),
    @PlayerName varchar(70),
    @PlayerNameEnglish varchar(70),
    @Age varchar(200),
    @Gender varchar(100),
    @Mobile1 varchar(20),
    @Mobile2 varchar(20)
    as
    set nocount on
    begin
     Declare @BaseQuery nvarchar(max) 
    ='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish, 
                             Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
    FROM            Evaluation.Players INNER JOIN
                             Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
        , @ParamList nvarchar(max) = N'@MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish varchar(70),@Age varchar(200), @Gender varchar(100), @Mobile1 varchar(20) , @Mobile2 varchar(20)'
        , @WhereClause nvarchar(max) = ' WHERE 1=1';

    IF @MembershipNumber IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = '+@MembershipNumber+'';
        END


    IF @PlayerName IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = '+@PlayerName+'';
        END


    IF @PlayerNameEnglish IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = '+@PlayerNameEnglish+'';
        END


    IF @Age IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in ('+@Age+')';
        END


    IF @Gender IS NOT NULL
        BEGIN

        declare @genderlist nvarchar(1000)
        declare @genderlist2 table (genders nvarchar(1000))
        declare @temp int
        INSERT INTO @genderlist2(genders)
        SELECT Split.a.value('.', 'NVARCHAR(MAX)')  as a

        FROM
        (
            SELECT CAST('&lt;X&gt;'+REPLACE(@Gender, ',', '&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;' AS XML) AS String
        ) AS A
        CROSS APPLY String.nodes('/X')
         AS Split(a)

         set @genderlist=(
        select ''''+genders+''',' from @genderlist2
        for xml path(''))
        select @genderlist = left(@genderlist,len(@genderlist)-1);

            SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in ('+@genderlist+')';
        END



        IF @Mobile1 IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = '+@Mobile1+'';
        END


        IF @Mobile2 IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = '+@Mobile2+'';
        END

        SET @BaseQuery = @BaseQuery + @WhereClause;


       EXECUTE sp_executesql @BaseQuery, @ParamList,
         @MembershipNumber = @MembershipNumber,
         @PlayerName = @PlayerName,
         @PlayerNameEnglish = @PlayerNameEnglish,
         @Age = @Age,
         @Gender = @Genderlist,
         @Mobile1 = @Mobile1,
         @Mobile2 = @Mobile2;

    end         


SQL Server 2016 Full procedure

     ALTER Procedure [Evaluation].[SelectPlayerData]

@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish varchar(70),
@Age varchar(200),
@Gender varchar(100),
@Mobile1 varchar(20),
@Mobile2 varchar(20)
as
set nocount on
begin
 Declare @BaseQuery nvarchar(max) 
='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish, 
                         Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
FROM            Evaluation.Players INNER JOIN
                         Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
    , @ParamList nvarchar(max) = N'@MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish varchar(70),@Age varchar(200), @Gender varchar(100), @Mobile1 varchar(20) , @Mobile2 varchar(20)'
    , @WhereClause nvarchar(max) = ' WHERE 1=1';

IF @MembershipNumber IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = '+@MembershipNumber+'';
    END


IF @PlayerName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = '+@PlayerName+'';
    END


IF @PlayerNameEnglish IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = '+@PlayerNameEnglish+'';
    END


IF @Age IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in ('+@Age+')';
    END


IF @Gender IS NOT NULL
    BEGIN
    DECLARE @genderlist nvarchar(1000);
    SET @genderlist=(select ''''+ value+''',' from string_split(@Gender,',')
    FOR XML PATH(''));
    SELECT @genderlist = left(@genderlist,len(@genderlist)-1);

        SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in ('+@genderlist+')';
    END



    IF @Mobile1 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = '+@Mobile1+'';
    END


    IF @Mobile2 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = '+@Mobile2+'';
    END

    SET @BaseQuery = @BaseQuery + @WhereClause;


   EXECUTE sp_executesql @BaseQuery, @ParamList,
     @MembershipNumber = @MembershipNumber,
     @PlayerName = @PlayerName,
     @PlayerNameEnglish = @PlayerNameEnglish,
     @Age = @Age,
     @Gender = @Genderlist,
     @Mobile1 = @Mobile1,
     @Mobile2 = @Mobile2;

end         


Test 1

create schema Evaluation
        create table Evaluation.Players(       PlayerID int,  MembershipNumber int,  PlayerName nvarchar(255),  PlayerNameEnglish nvarchar(255),  Age varchar(200), 
                          Mobile1 char(15),Mobile2 char(15) , GenderID tinyint)

        create table Evaluation.Gender(GenderID tinyint, GenderEnglish nvarchar(255))

        insert into Evaluation.players VALUES (1,1,'Randi','Randi',12,123214215215,12421421422,1)
        insert into Evaluation.players VALUES (2,2,'Gandalf','Gandalf',142,123214215215,12421421422,1)
        insert into Evaluation.players VALUES (3,3,'Gandalf','Frodo',14,123214215215,12421421422,1)

        insert into Evaluation.gender VALUES(1,'Male')
        insert into Evaluation.gender VALUES(2,'Female')


DECLARE @return_value int

EXEC    @return_value = Evaluation.[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = '12,14',
        @Gender =  NULL,
        @Mobile1 = NULL,
        @Mobile2 = NULL
   SELECT  'Return Value' = @return_value


Result

&gt;  PlayerID MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
&gt;     1 1   Randi   Randi   12  Male    123214215215    12421421422    
&gt;     3 3   Gandalf Frodo   14  Male    123214215215    12421421422


Test 2

Extra insert

INSERT INTO Evaluation.players VALUES (4,4,'Emma','Watson',12,123214215215,12421421422,2);


Execute proc

 DECLARE @return_value int
EXEC    @return_value = Evaluation.[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = '12,14',
        @Gender =  'Male,Female',
        @Mobile1 = NULL,
        @Mobile2 = NULL


Result

PlayerID    MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
1   1   Randi   Randi   12  Male    123214215215    12421421422    
3   3   Gandalf Frodo   14  Male    123214215215    12421421422    
4   4   Emma    Watson  12  Female  123214215215    12421421422