Another Reason to be Thankful for SQL VALUES

 Last year, I highlighted the performance advantages of writing tableless queries with the SQL VALUES support. In the spirit of the upcoming US Thanksgiving holiday, I want to point out another reason developers can be thankful for the VALUES support in SQL.

The VALUES support comes in two flavors, VALUES & VALUES INTO, just like the SQL SELECT syntax. The VALUES INTO and SELECT INTO statements enable you to run a query that generates a single row and assign the values in that row to variables. While both statements provide the same capability, the VALUES INTO statement has the added advantage that it can be dynamically prepared and executed. This advantage is a big one for SQL developers because it allows you to implement a solution with less SQL code.

To help you understand why developers should be thankful for the dynamic SQL support of VALUES INTO, let’s look at the hoops you had to jump through prior to the VALUES support. Since the SELECT INTO statement could not be dynamically prepared, the only way to assign the results of a dynamic query to variables was through the usage of a dynamic SQL cursor. 

Let’s assume that a program needs to return the count of rows in a table (e.g., MYTABLE) and dynamic SQL is required because that table can reside in many different libraries. The SELECT version of this solution would look like the following set of code. First, the count query statement text is assembled and prepared. Next, a cursor for that prepared statement must be declared and then that cursor must be opened, fetched, and closed.

STMTTXT = 'SELECT COUNT(*) FROM ' TBLIB + '/MYTABLE';

EXEC SQL 
   PREPARE QRY_ROWCOUNT FROM :STMTTXT;

EXEC SQL 
   DECLARE ROWCOUNT_CUR CURSOR FOR QRY_ROWCOUNT;

EXEC SQL 
   OPEN ROWCOUNT_CUR;

EXEC SQL 
   FETCH ROWCOUNT_CUR INTO :ROWCOUNT;

EXEC SQL
    CLOSE ROWCOUNT_CUR;

A lot of code and work to just run and save the results of a simple row count query. 

Now, let’s examine how the VALUES INTO statement provides for a simpler solution. The fact that this VALUES INTO implementation requires half as much code should make it clear why this is a preferred solution.

STMTTXT = 'VALUES (SELECT COUNT(*) FROM ' TBLIB + '/MYTABLE) INTO ? ';

EXEC SQL
   PREPARE QRY_ROWCOUNT FROM :STMTTXT;

EXEC SQL
   EXECUTE QRY_ROWCOUNT USING :ROWCOUNT;

 

The VALUES INTO support enables the simple execution of a dynamic query without the coding overhead of using an SQL cursor.

The fewer lines that you have to code means less code that you have to test and maintain going forward. And those are great reasons to be thankful if you’re an SQL developer!