Prevent Salary Credit with Invalid Data

We have an requirement that to prevent salary credit with invalid data. Business Requirement

Before running the monthly salary credit job, the system must ensure the no employee has NULL bank account or no employee has salary = 0 or negative. If validation fails our job must STOP immediately and Error message must show which employee caused the failure. This is a hard stop, not just a warning.

For this demo create a table and inserting few valid and invalid records.

CREATE TABLE EmployeeSalary (     EmpId INT,     EmpName VARCHAR(50),     BankAccountNo VARCHAR(20),     Salary DECIMAL(10,2) ); --inserting few records INSERT INTO EmployeeSalary VALUES (101, 'Amit', 'ACC123', 50000), (102, 'Ravi', 'ACC134', 45000),     (103, 'Neha', 'ACC789', 12000), (104, 'Bagesh',null,25000), (105, 'Ramesh', 'ACC788', 0);

 See the records into the table.

Now we are creating the SP which validate the data and credit the salary amount.

CREATE PROCEDURE dbo.ProcessSalaryCredit AS BEGIN     SET NOCOUNT ON;      DECLARE @EmpId INT, @EmpName VARCHAR(50);      -- Check for invalid salary records     SELECT TOP 1            @EmpId = EmpId,            @EmpName = EmpName     FROM EmployeeSalary     WHERE BankAccountNo IS NULL        OR Salary <= 0;      IF @EmpId IS NOT NULL     BEGIN         RAISERROR (             'Salary processing failed. Invalid data found for EmpId: %d, Name: %s',             16,   -- Severity (stops execution)             1,    -- State             @EmpId,             @EmpName         );          RETURN; -- Extra safety     END      -- Actual salary credit logic     PRINT 'Salary credited successfully for all employees'; END;

SP created successfully.

Now running this SP.

EXEC dbo.ProcessSalaryCredit;

It throws an error. It will stop Job immediately and produce Clear error for support & DBA and there is no partial salary credit.