Insert into using a Case Statement

Question

I am trying to insert data into a table using a stored procedure with one parameter. The parameter is looking for the name of a club. I want the SP to find all the players that belong to the club and insert them into the ClubNameTable. The problem is when I run the SP it inserts all the data from the join into the table, not just when the case statement is true.

CREATE PROCEDURE SSIS.usp_ClubNameTable 
    -- Add the parameters for the stored procedure here
    @NameOfClub nvarchar(200)
AS
BEGIN
    TRUNCATE TABLE SSIS.ClubNameTable;
    insert into ssis.ClubNameTable ([NameOfClub], [FirstName], [LastName])
        SELECT
            p.FirstName,
            COUNT(p.LastName) as 'Number of Last Names',                
            CASE
                WHEN @NameOfClub = 'Eagle Plate' THEN NameOfClub
                WHEN @NameOfClub = 'Atlanta United FC' THEN NameOfClub
                    ELSE 'Please choose either Eagle Plate or Atlanta United FC'
                end
    FROM [Location].Club as C
            JOIN [Location].ClubDetails as CD on c.ClubID = cd.ClubID
            JOIN [Player].Player as P on CD.PlayerID = P.PlayerID
            GROUP BY c.NameOfClub, p.FirstName, p.LastName
            ORDER BY p.LastName ASC
        declare 
        @RowCount int
            SET @RowCount = (SELECT COUNT(*) FROM Location.Club)
END
GO

It is very hard to tell what the issue is without actually seeing the code of the stored procedure, sample data and expected output, would you please provide those? — GMB
– GMB, Commented Mar 17, 2019 at 20:10
Sorry I hit enter without putting the code on, just edited it. Anything else to make it more clear? — Max Taylor-Hayden
– Max Taylor-Hayden, Commented Mar 17, 2019 at 20:11
Don't copy data around between the tables. Each player should be stored only once in the database. — jarlh
– jarlh, Commented Mar 17, 2019 at 20:31
This looks like business logic. Would it be easier to do this logic prior to sending it to sql? — Andy Danger Gagne
– Andy Danger Gagne, Commented Mar 17, 2019 at 21:21

Amir Molaei · Accepted Answer · 2019-03-18 05:19:26Z

3

Using case statement on a column does not do any filtering on records, it just applies conditions on the value that is to be assigned to the column. In order to filter records you need to use a where clause with the desired conditions:

 CREATE PROCEDURE SSIS.usp_ClubNameTable 
    -- Add the parameters for the stored procedure here
    @NameOfClub nvarchar(200)
AS
BEGIN
    TRUNCATE TABLE SSIS.ClubNameTable;
    if(@NameOfClub = 'Eagle Plate' or @NameOfClub = 'Atlanta United FC')
    begin
    insert into ssis.ClubNameTable ([NameOfClub], [FirstName], [LastName])
        SELECT
            p.FirstName,
            COUNT(p.LastName) as 'Number of Last Names',                
            NameOfClub
    FROM [Location].Club as C
            JOIN [Location].ClubDetails as CD on c.ClubID = cd.ClubID
            JOIN [Player].Player as P on CD.PlayerID = P.PlayerID
            Where C.NameOfClub = @NameOfClub
            GROUP BY c.NameOfClub, p.FirstName, p.LastName
            ORDER BY p.LastName ASC
        declare 
        @RowCount int
            SET @RowCount = (SELECT COUNT(*) FROM Location.Club)
    end

    else
    begin
    select 'Please choose either Eagle Plate or Atlanta United FC'
    end
END
GO

edited Mar 18, 2019 at 5:19

answered Mar 17, 2019 at 20:32

Amir Molaei

3,8601 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Taylor-Hayden Over a year ago

Thank you, but this doesn't seem to work for me.When i run this SP, it doesnt matter what i enter for the parameter, the target table always contains a mixture of both clubs.

Collectives™ on Stack Overflow

Insert into using a Case Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related