Browse Prior Art Database

EXEC Command for Dynamic SQL Statements

IP.com Disclosure Number: IPCOM000183931D
Original Publication Date: 2009-Jun-05
Included in the Prior Art Database: 2009-Jun-05
Document File: 4 page(s) / 26K

Publishing Venue

IBM

Abstract

An extension to the SQL language that provides a new (enhanced) EXEC command is proposed. This new EXEC command will dynamically process and execute: 1) the results returned from the execution of a SQL query statement, or 2) a single scalar string that contains a SQL statement. This extension to the SQL language could be implemented as a proprietary extension of the language provided by the database vendor or as a formal part of the SQL language.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 33% of the total text.

Page 1 of 4

EXEC Command for Dynamic SQL Statements

Currently, the SQL language does not provide a mechanism to execute SQL statements that are 1) built by concatenating variable values and string literals or that result from the execution of a SQL statement (e.g., from the execution of a SQL query, you can generate a set of strings where each one contains a valid SQL statement).

Having said this, it is true that currently you can use prepared statements to execute a SQL statement with different parameters at different times but this very different from having the capability to process SQL statements that are built dynamically, for instance, as the result of executing a SQL query. It is also true that currently you can build your SQL dynamically (http://www.sommarskog.se/dynamic

                                    sql.html#Introducing) but this requires the use of a formal programming language such as C or procedural SQL. For instance, the current mechanism for building SQLs dynamically inside a procedural SQL function allows the execution of a the string that is created by appending literals and [procedural] variables defined within the function:

CREATE PROCEDURE general

_

select @tblname varchar(128), @key

         EXEC('SELECT col1, col2, col3 FROM ' + @tblname + ' WHERE keycol = ''' + @key + '''')

The EXEC command shown above does execute the dynamic SQL built by concatenation. However, it cannot take as an input parameter the resulting set of strings (i.e.,one or more table varchar rows) that could be generated by executing a SQL select statement. If you try to do something like this, it would simply fail. For instance, if you had a SQL query statement that generated a table with a varchar column, the current EXEC statement won't process the data contained in the resulting table.

There are many cases where it would be very beneficial to have an EXEC command that can be run as a "stand-alone" SQL command (i.e., SQL outside a stored procedure and/or user defined function) that takes as input a table resulting from the execution of a SQL select statement. It could be very desirable to execute a SQL query and then have the resulting data (table rows) be executed/processed by the EXEC command (note: the resulting output table generated by the execution of the SQL query would contain a single column [of type varchar] and would be the input to the EXEC command). Having such functionality would, for instance, simplify the logic needed to reset the primary key value generation for several tables in a database.

The proposed EXEC statement would accept the following input (type): A single-column table that results from the execution of a SQL select/query statement. The type of this column shall be a string (varchar or char). If it is not, the EXEC command will try to cast it to a varchar type (if this fails, an error would be thrown). The string row records [resulting from the execution of the SQL select statement] that are passed as input (i.e., table...