SQL Server存储过程和Nest JS API中的多参数问题

Question

存储过程:

ALTER Procedure [dbo].[API_Insert_Break]
@EmployeeId int,
@BreakTypeTitle nvarchar(200)=null,
@UpdationIP varchar(20)=null,
@Reason nvarchar(200)=null,
@BreakInIP nvarchar(50)=null,
@BreakOutIP nvarchar(50)=null

as begin
if (@BreakOutIP is null)
begin
INSERT INTO AttendanceBreaks(AttendanceId, 
BreakTypeId, 
StartAt, 
BreakInIPAddress, 
Reason,
CreationDate,
CreatedBy,
UpdationIP)
VALUES ((select top 1 Id from Attendances where EmployeeId=@EmployeeId order by Id desc), 
(select BreakTypes.Id from BreakTypes where BreakTypes.Title=@BreakTypeTitle), 
(SELECT convert(time(0),getutcDate())), 
@BreakInIP, 
@Reason,
getutcdate(),
@EmployeeId,
@UpdationIP);
end
else
begin
Update AttendanceBreaks set EndAt=(SELECT convert(time(0),getutcDate())), 
DurationInMinutes=DATEDIFF(minute,StartAt,(SELECT convert(time(0),getutcDate()))), 
BreakOutIPAddress=@BreakOutIP,
UpdationDate=getutcdate(),
UpdationBy=@EmployeeId,
UpdationIP=@UpdationIP
where Id=(select MAX(Id) from AttendanceBreaks where AttendanceId=(select MAX(Id) from Attendances where EmployeeId=@EmployeeId));
end
end

NestJS服务代码:

async breakStart(employeeId:number,breakTypeTitle:string,reason:string): Promise<any> {
var data = await this.sequelize.query(
'Exec API_Insert_Break :EmployeeId, :BreakTypeTitle, :UpdationIP, :Reason, :BreakInIP',
{
replacements: { 
EmployeeId: employeeId,
BreakTypeTitle:breakTypeTitle,
UpdationIP:ip.address(),
Reason:reason,
BreakInIP:ip.address()},
type: sequelize.QueryTypes.INSERT
});
}




async breakEnd(employeeId:number): Promise<any> {
var data = await this.sequelize.query(
'Exec API_Insert_Break :EmployeeId, :BreakTypeTitle, :UpdationIP, :Reason, :BreakInIP, :BreakOutIP',
{
replacements: { 
EmployeeId: employeeId,
UpdationIP:ip.address(),
BreakOutIP:ip.address()

},
type: sequelize.QueryTypes.INSERT
});
}

问题是要运行else语句，BreakOutIP不能为空。但是由于BreakOutIP位于最后(作为参数声明)，因此跳过了中间的一些参数，它跳转到最后一个参数。因此，如果我传递一个值给BreakOutIP，该值没有传递给它，结果，它仍然运行第一个if语句(而不是else语句)，这需要BreakOutIP为空。因为我听说在存储过程中传递的所有参数必须按照正确的顺序，不应该跳过任何值，否则它将无法正常运行． 所以有人能告诉我如何克服这个问题吗?我的意思是如何正确地传递参数，使所有的输入值读取没有任何冲突?

Answer 1

我找到问题了。问题是，Nest JS代码和存储过程的参数顺序必须相同。在Nest JS代码中不能跳过任何参数。所以我传入了null

async breakEnd(employeeId:number): Promise<any> {
var data = await this.sequelize.query(
'Exec API_Insert_Break :EmployeeId, :BreakTypeTitle, :UpdationIP, :Reason, :BreakInIP, :BreakOutIP',
{
replacements: { 
EmployeeId: employeeId,
------>  BreakTypeTitle:null,
UpdationIP:ip.address(),
------>  Reason:null,
------>  BreakInIP:null,
BreakOutIP:ip.address()

},
type: sequelize.QueryTypes.INSERT
});
}