SQL GET

What does the SQL GET keyword do?

GET is a reserved word used in statements like GET DIAGNOSTICS and GET DESCRIPTOR to fetch runtime diagnostic, descriptor, or environment information inside stored programs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GET: Standard SQL/PSM (GET DIAGNOSTICS, GET DESCRIPTOR) PostgreSQL (PL/pgSQL) IBM Db2 Oracle (limited: only GET DIAGNOSTICS in PL/SQL via SQL%ROWCOUNT equivalent) SQL Server (only proprietary GETDATE/GETUTCDATE functions, not GET DIAGNOSTICS) MySQL and SQLite – no procedural GET statements

SQL GET Full Explanation

GET is not a stand-alone executable command. Instead, it prefixes compound statements defined in the SQL standard and several vendor dialects. The two most common standard forms are:1. GET DIAGNOSTICS – retrieves the number of rows affected, SQLSTATE, and other condition information after a preceding SQL statement, typically inside stored procedures or functions.2. GET DESCRIPTOR – copies data from an SQL descriptor area (SQLDA) into host variables in embedded SQL applications.Vendors also introduce proprietary GET statements and functions, for example:- SQL Server: GETDATE(), GETUTCDATE() scalar functions.- PostgreSQL: GET DIAGNOSTICS (fully supported in PL/pgSQL).- IBM Db2: GET DIAGNOSTICS, GET DIAGNOSTICS EXCEPTION.Because GET is reserved, it cannot be used as an identifier unless quoted. The keyword is available only inside procedural or embedded SQL contexts, not in ad-hoc queries run directly against a database.

SQL GET Syntax

-- Standard SQL/PSM example
GET DIAGNOSTICS variable1 = ROW_COUNT,
                variable2 = RETURNED_SQLSTATE;

-- Embedded SQL example
GET DESCRIPTOR :sqlda VALUE :index :host_variable;

-- SQL Server scalar function (proprietary)
SELECT GETDATE();

SQL GET Parameters

variable = item_name - local variable receiving the requested diagnostic item
sqlda - descriptor name or host variable referencing an SQLDA
VALUE (index) - 1-based column index in the descriptor
host_variable - program variable to receive the value

Example Queries Using SQL GET

-- 1. Capture affected-row count after an UPDATE in PostgreSQL
UPDATE users SET active = false WHERE last_login < CURRENT_DATE - INTERVAL '90 days';
GET DIAGNOSTICS v_row_count = ROW_COUNT;

-- 2. Retrieve SQLSTATE of the last statement in Db2
GET DIAGNOSTICS v_state = RETURNED_SQLSTATE;

-- 3. Embedded C program snippet using GET DESCRIPTOR
EXEC SQL ALLOCATE DESCRIPTOR mydesc;
EXEC SQL DESCRIBE SELECT LIST FOR s1 INTO SQL DESCRIPTOR mydesc;
EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :col_name;

-- 4. SQL Server proprietary GETDATE() usage
SELECT GETDATE() AS current_ts;

Expected Output Using SQL GET

v_row_count contains the integer number of rows updated.
v_state holds the five-character SQLSTATE string.
col_name host variable receives the column name of the first select-list item.
Query returns one row with the current server timestamp.

Use Cases with SQL GET

Error handling and logging in stored procedures
Branching logic based on ROW_COUNT (e.g., raise error if 0 rows affected)
Retrieving dynamic metadata in embedded SQL applications
Obtaining the current timestamp in SQL Server without referring to system tables

Common Mistakes with SQL GET

Trying to run GET DIAGNOSTICS outside a procedural block – it will fail.
Forgetting to allocate a descriptor before GET DESCRIPTOR.
Using GET as a table or column name without quoting.
Assuming GETDATE() exists in MySQL or PostgreSQL – it is SQL Server specific.

First Introduced In

ISO/IEC SQL/1999 (Persistent Stored Modules)

Frequently Asked Questions

Is GET a standalone SQL command?

No. GET only appears as part of compound statements like GET DIAGNOSTICS or GET DESCRIPTOR.

What is GET DIAGNOSTICS used for?

It fetches diagnostic information such as ROW_COUNT, SQLSTATE, or MESSAGE_TEXT after a preceding SQL operation inside stored programs.

Which databases support GET DIAGNOSTICS?

PostgreSQL (PL/pgSQL), IBM Db2, and the SQL standard. SQL Server and MySQL do not offer it, though SQL Server provides GETDATE().

How can I find the number of affected rows without GET DIAGNOSTICS?

In many dialects you can use dialect-specific constructs: SQL%ROWCOUNT in Oracle PL/SQL, @@ROWCOUNT in SQL Server, or FOUND in PostgreSQL.