Prev | SQL Engine Reference | Next |
Global Variables
Pervasive.SQL V8 supports the following global variables:
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 @@IDENTITYReturns 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 = 12Returns 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 + 10Updates 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;CALL TEST (@@IDENTITY)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;@@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 @@ROWCOUNTReturns 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 T1SELECT @@ROWCOUNT FROM T12The 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.
Prev Scalar Functions |
Contents Up Check for Revisions | Next Other Characteristics |