我正在尝试使用带有一个参数的存储过程将数据插入表中。该参数正在寻找俱乐部的名称。我希望SP找到所有属于俱乐部的球员,并将其插入俱乐部。问题是当我运行SP时,它将所有数据从联接中插入表中,而不仅仅是案例语句为真时。
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
在列上使用case statement
在记录上不进行任何过滤,它只是在要分配给列的值的条件上。为了过滤记录,您需要在所需条件下使用where
子句:
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