How to know whether record is updated or not

When you do an update, it's immediately return rows updated: It Return Number of Records Affected

@@ROWCOUNT - Returns the number of rows affected by the last statement and Return type of @@ROWCOUNT is int.

The following example executes an UPDATE statement and uses @@ROWCOUNT to detect if any rows were changed

UPDATE Tbl_EmpProfile  SET JobTitle = 'Executive' WHERE EMPID = 5001
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';

You can set this @@rowcount to some other variable and use them for further processing.

Example,

DECLARE @rows AS INT
UPDATE Tbl_EmpProfile  SET JobTitle = 'Executive' WHERE EMPID = 5001
SET @rows = @@rowcount

using Stored Procedure, Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle.

if you have multiple INSERT/UPDATE/DELETEs, you'll need a variable to store the result from @@ROWCOUNT for each operation.