Use Parameter Markers in SQL Persistent Stored Modules

June 4, 2008 Hey Mike

I have read your articles about SQL stored procedures with great interest. A year ago I tried to use parameter markers in dynamic SQL stored procedures but couldn’t succeed. I ended up writing stored procedures in COBOL. Could you please provide an example of dynamic SQL stored procedures using parameter markers?

–Venkat

Parameter markers in SQL Persistent Stored Modules are very easy to implement. If you’re using embedded SQL in COBOL to prepare dynamic SQL statements, you’re probably already 99 percent there.

Below is an example of an SQL stored procedure that uses dynamic SQL to create a cursor and return a result set to the caller. The example is simple but you can extend the concept to work with all preparable SQL statements that can use parameter markers.

Create Procedure DynamicTest()
Language SQL
Result Sets 1
Set Option Commit=*None, UsrPrf=*Owner,
           DynUsrPrf=*Owner, DatFmt=*ISO
Begin

Declare @CusNum Numeric(6,0) Not Null Default 938472;
Declare @SQL    VarChar(2048) Not Null Default '';
Declare QueryCursor Cursor For PrepQuery;
Set @SQL='Select * From QIWS/QCUSTCDT Where CusNum=?';

Prepare PrepQuery From @SQL;
Open QueryCursor Using @CusNum;
End

Here is a little run down of the code. The following string variable holds a dynamic SQL query. In a dynamic SQL statement, a parameter marker is indicated with the question mark symbol.

Set @SQL='Select * From QIWS/QCUSTCDT Where CusNum=?';

A query is converted from character format into an executable form by using the Prepare statement:

Prepare PrepQuery From @SQL;

A cursor is declared based on the prepared statement:

Declare QueryCursor Cursor For PrepQuery;

Note that when writing SQL routines, the Declare Cursor statement has to be placed in the declaration section of the code block. In other words, along with the other Declare statements, it must come before the first executable line of code in the current code block. Each code block is enclosed within a BEGIN and END.

Finally, when the cursor is opened all variables for the parameter markers must be supplied in the Using clause of the Open statement:

Open QueryCursor Using @CusNum;

Prior to V5R3, the data type of the host variable needed to be compatible with the data type of the parameter marker in the prepared statement because V5R3, DB2 will attempt to convert the value to match the data type required by the statement. This means that @CusNum could be a CHAR(6) variable instead of numeric (i.e., zoned decimal) variable. As long as the variable contains data that can be converted to numeric the statement will succeed.

When the cursor is left open, the caller will be able to retrieve the query results. Optionally, when working with multiple result sets, the SET RESULT SETS statement can be used to control the order that the result sets are returned to the caller.

And that is all there is to executing a parameterized dynamic SQL statement within an SQL stored procedure. Be careful about using parameter markers with dynamic SQL, as the extra overhead of processing the parameter may not be worth the performance hit. Generally parameter markers are only beneficial in cases when the same SQL statement will be executed over again with the same execution plan being utilized by the query engine.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Visit the IT Jungle Contact page to email Michael Sansoterra.