bd_proc_saveRepair Stored Procedure - Save Repair Information

ALTER PROCEDURE [dbo].[bd_proc_saveRepair] ( @orderNo VARCHAR(50) ,@orderId BIGINT ,@userSerial BIGINT ,@pcIp VARCHAR(20) ,@repairStartDate VARCHAR(100) ,@repairEndDate VARCHAR(100) ,@startTime INT ,@endTime INT ,@userIds VARCHAR(MAX) ,@approver1 BIGINT ,@approver2 BIGINT ,@applyType INT ,@remark VARCHAR(MAX) ,@repairType INT ,@orderNumber VARCHAR(50) -- 【杨雯雯 0520】 ,@approverId VARCHAR(100) = '' ,@approveTime DATETIME = NULL ,@operateTime DATETIME = NULL ,@firstApproverUserNoAndNameList VARCHAR(MAX) ,@secondApproverUserNoAndNameList VARCHAR(MAX) ) AS BEGIN

DECLARE @users TABLE ( user_serial BIGINT )

INSERT INTO @users(user_serial) SELECT value FROM STRING_SPLIT(@userIds,',') -- Check if the order number already exists for the given repair type IF EXISTS(SELECT 1 FROM dbo.bd_repair WHERE order_no = @orderNo AND repair_type = @repairType) BEGIN RAISERROR('Order number already exists for the given repair type', 16, 1) RETURN END

-- Check if the order number already exists for the given order type and repair type IF EXISTS(SELECT 1 FROM dbo.bd_repair WHERE order_id = @orderId AND repair_type = @repairType AND apply_type = @applyType) BEGIN RAISERROR('Order number already exists for the given order type and repair type', 16, 1) RETURN END

-- Check if the user serials exist in the user table IF NOT EXISTS(SELECT 1 FROM dbo.bd_user WHERE user_serial IN (SELECT user_serial FROM @users)) BEGIN RAISERROR('Invalid user serials', 16, 1) RETURN END

-- Check if the approver1 exists in the user table IF NOT EXISTS(SELECT 1 FROM dbo.bd_user WHERE user_serial = @approver1) BEGIN RAISERROR('Invalid approver1', 16, 1) RETURN END

-- Check if the approver2 exists in the user table IF NOT EXISTS(SELECT 1 FROM dbo.bd_user WHERE user_serial = @approver2) BEGIN RAISERROR('Invalid approver2', 16, 1) RETURN END

-- Insert into the repair table INSERT INTO dbo.bd_repair ( order_no, order_id, user_serial, pc_ip, repair_start_date, repair_end_date, start_time, end_time, approver1, approver2, apply_type, remark, repair_type, order_number, approver_id, approve_time, operate_time, first_approver_user_no_and_name_list, second_approver_user_no_and_name_list ) VALUES ( @orderNo, @orderId, @userSerial, @pcIp, @repairStartDate, @repairEndDate, @startTime, @endTime, @approver1, @approver2, @applyType, @remark, @repairType, @orderNumber, @approverId, @approveTime, @operateTime, @firstApproverUserNoAndNameList, @secondApproverUserNoAndNameList )

END