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('<X>'+REPLACE(@Gender, ',', '</X><X>')+'</X>' 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('<X>'+REPLACE(@Gender, ',', '</X><X>')+'</X>' 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

>  PlayerID MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
>     1 1   Randi   Randi   12  Male    123214215215    12421421422    
>     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    
  • unfortunately, I am using SQL server 2012 – Kerols Alfons Dec 6 at 10:48
  • There is also an other issue with th variables in your where clause not parsing. I will update my solution. – Randi Vertongen Dec 6 at 11:18
  • "string_split" does not work on my server, But I can use the GenderID filtered instead of Gender ;) – Kerols Alfons Dec 6 at 12:37
  • Yes, and that part should be changed to a custom split, like the one @ScottHodgin showed. The other issue I had when testing your proc and it not giving results was due to the parameters in the where clause not being escaped. – Randi Vertongen Dec 6 at 12:39
  • 1
    Yes, I have changed the procedure with your code and added the gender to be GenderID and it is working properly, Thank you – Kerols Alfons Dec 6 at 12:46

You could probably achieve your goal by using a SPLIT table valued function similar to the answer here - passing multiple values to single parameter for inline queries

DECLARE @SQL NVARCHAR(2000)

SET @SQL = 'SELECT account FROM invoice WHERE account IN(Select splitdata from [fnSplitString](@AccountList,'',''))'

EXEC sp_executesql @SQL
    ,N'@AccountList varchar(5000)'
    ,@AccountList = 'AN565,AT060'

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.