SQL Reference - MODIFIES

MODIFIES

MODIFIES SQL DATA ACCESS is a statement in SQL procedures that specifies the kind of database access the procedure provides. Essentially, it declares if the procedure contains statements that modify SQL data. It is used to ensure data integrity and manage the interaction of procedures with the database.

Example

DECLARE
   a INT := 10;
   b INT := 20;
BEGIN
   DBMS_OUTPUT.PUT_LINE ('The old value of a is: ' || to_char(a));
   a := b;
   DBMS_OUTPUT.PUT_LINE ('The new value of a is: ' || to_char(a));
END;

Output

The old value of a is: 10
The new value of a is: 20

Explanation

In the above example, the variable ‘a’ originally had the value of 10. The statement ‘a := b;’ modifies the value of ‘a’, assigning it the value of ‘b’ which is 20. This modification of the variable ‘a’ is reflected in the output.

Example

DECLARE @Value1 INT, @Value2 INT;
SET @Value1 = 14;
SET @Value2 = 5;
SELECT @Value1 % @Value2 AS Result;

Output

Result
4

Explanation

The given SQL script calculates the remainder of the division between @Value1 (14) and @Value2 (5) using the MOD operator (%) in SQL Server. The result, 4, is stored in the alias Result.