Global Variables

---

Pervasive.SQL V8 supports the following global variables:

• @@IDENTITY
• @@ROWCOUNT

Either variable can be prefaced with two at signs (@@) or an at sign and a colon (@:). For example, @@IDENTITY and @:IDENTITY are equivalent.

@@IDENTITY and @@ROWCOUNT are global variables per connection. Each database connection has its own @@IDENTITY and @@ROWCOUNT values.

@@IDENTITY

This variable returns the value of the most recently inserted IDENTITY column value (IDENTITY or SMALLIDENTITY). The value is a signed integer value. The initial value is NULL.

This variable can only refer to a single column. If the target table includes more than one IDENTITY column, the value of this variable refers to the IDENTITY column that is the table's primary key. If no such column exists, then the value of this variable refers to the first IDENTITY column in the table.

If the most recent insert was to a table without an IDENTITY column, then the value of @@IDENTITY is set to NULL.

Examples

SELECT @@IDENTITY 

Returns NULL if no records have been inserted in the current connection, otherwise returns the IDENTITY column value of the most recently inserted row.

SELECT * FROM T1 WHERE @@IDENTITY = 12 

Returns the most recently inserted row if it has an IDENTITY column value of 12. Otherwise, returns no rows.

INSERT INTO T1(C2) VALUES (@@IDENTITY) 

Inserts the IDENTITY value of the last row inserted into column C2 of the new row.

UPDATE T1 SET T1.C1 = (SELECT @@IDENTITY) WHERE T1.C1 = @@IDENTITY + 10 

Updates column C1 with the IDENTITY value of the last row inserted, if the value of C1 is 10 greater than the IDENTITY column value of the last row inserted.

UPDATE T1 SET T1.C1 = (SELECT NULL FROM T2 WHERE T2.C1 = @@IDENTITY) 

Updates column C1 with the value NULL if the value of C1 equals the IDENTITY column value of the last row inserted.

The example below creates a stored procedure and calls it. The procedure sets variable V1 equal to the sum of the input value and the IDENTITY column value of the last row updated. The procedure then deletes rows from the table anywhere column C1 equals V1. The procedure then prints a message stating how many rows were deleted.

CREATE PROCEDURE TEST (IN :P1 INTEGER); 
BEGIN 
DECLARE :V1 INTERGER; 
SET :V1 = :P1 + @@IDENTITY; 
DELETE FROM T1 WHERE T1.C1 = :V1; 
IF (@@ROWCOUNT = 0) THEN 
PRINT 'No row deleted'; 
ELSE 
PRINT CONVERT(@@ROWCOUNT, SQL_CHAR) + 
' rows deleted'; 
END IF; 

END; 

CALL TEST (@@IDENTITY) 

@@ROWCOUNT

This variable returns the number of rows that were affected by the most recent operation in the current connection. The value is an unsigned integer. The initial value is zero.

Grammar

Same as the grammar for @@IDENTITY.

Examples

SELECT @@ROWCOUNT 

Returns zero if no records were affected by the previous operation in the current connection, otherwise returns the number of rows affected by the previous operation.

CREATE TABLE T1 (C1 INTEGER, C2 INTEGER) 
INSERT INTO T1 (C1, C2) VALUES (100,200) 
INSERT INTO T1(C2) VALUES (100, @@ROWCOUNT) 
SELECT * FROM T1 

SELECT @@ROWCOUNT FROM T1 

Results:

2 

The first SELECT generates two rows and shows that the value of @@ROWCOUNT was 1 when it was used to insert a row. The second SELECT returns 2 as the value of @@ROWCOUNT, that is, after the first SELECT returned two rows.

Also see the example for @@IDENTITY.