存储过程中的更新

Question

我正在尝试接收员工的NIC号码，这是他们的ID号码，并根据时间段从一个团队更改为另一个团队。

所以，我有4个小组负责开发一个软件，每个员工可以在不同的时间参加不同的小组。例如，我有一个员工NIC号码54323，属于3个不同的组。但当我执行查询说1行受影响，但当我做select * from StuffTeams where NIC = 54323它没有改变任何东西。

CREATE OR ALTER PROCEDURE sp_TransferTeam
@NIC int,
@IDTeam1 int,
@IDTeam2 int,
@StartDate date,
@EndDate date
AS
BEGIN
SELECT @NIC = NIC 
FROM StuffTeams 
WHERE IDTeam = @IDTeam1 
UPDATE StuffTeams 
SET IDTeam = @IDTeam2 
WHERE NIC = @NIC 
AND DateStart = @StartDate 
AND DateFinish = @EndDate
END
EXEC sp_TransferTeam 54323, 4, 2, '2017-08-01', '2017-10-01'

StuffTeam表:

CREATE TABLE StuffTeams
(
IDStuffTeam nvarchar(30) NOT NULL,
IDTeam int NOT NULL,
NIC int NOT NULL,
DateStart date NOT NULL,
DateFinish date NOT NULL,
CONSTRAINT PK_Stuff PRIMARY KEY (IDStuffTeam),
CONSTRAINT FK_sTeam FOREIGN KEY (IDTeam) REFERENCES Teams (IDTeam),
CONSTRAINT FK_sNIC FOREIGN KEY (NIC) REFERENCES Employees (NIC),
)

在任何情况下，team table:

CREATE TABLE Teams 
(
IDTeam int NOT NULL,
IDProduct int NOT NULL,
TeamName nvarchar(30) NOT NULL,
DateStart date NOT NULL,
DateFinish date NOT NULL,
CONSTRAINT PK_Team PRIMARY KEY (IDTeam),
CONSTRAINT FK_IDProduct FOREIGN KEY (IDProduct) REFERENCES Products (IDProduct)
)

多谢

Answer 1

编辑你的sp

CREATE OR ALTER PROCEDURE sp_TransferTeam
@NIC int,
@IDTeam1 int,
@IDTeam2 int,
@StartDate date,
@EndDate date
AS
BEGIN
UPDATE StuffTeams 
SET IDTeam = @IDTeam2 
WHERE NIC = @NIC 
AND DateStart = @StartDate 
AND DateFinish = @EndDate
END
EXEC sp_TransferTeam 54323, 4, 2, '2017-08-01', '2017-10-01'

然后从外部选择查看结果

Answer 2

您应该将@NIC声明为变量而不是参数，然后在第一次查询时设置它。调试的一个好方法是使用SP之外的where子句选择记录，然后查看是否选择了您所针对的所有记录。

SELECT * FROM StuffTeams
WHERE NIC = 54323 
AND DateStart = '2017-08-01' 
AND DateFinish = '2017-10-01'

试着运行这个，看看它是否返回你想要更新的记录。where子句可能会将它们过滤掉。如果不是，请尝试将传递日期的日期格式更改为MM-DD-YYYY。