Re: SQL statements in a SQL Stored Procedure.

Carel,

No. Strange as it seems, when doing Persistent Stored Modules (PSM)
(ie. writing functions or procedures in SQL) you don't need to prefix
variable names.

When you think about it, it makes sense, after all do you have to
prefix RPG variables in RPG?

Charles

On Mon, Dec 21, 2009 at 5:13 PM, Carel Teijgeler <coteijgeler@xxxxxxxxx> wrote:

Should you not use an ampersand (&)  for the variable name, as in Query
Manager? or an colon (:) as in SQLRPG?

With regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 21-12-2009 at 14:58 Alan Campin wrote:

When you are executing SQL statements in an SQL Stored Procedure do you

have

to execute any SQL statements dynamically? I assumed you could just

include

the statements and the compiler would put in the calls automatically. Here
is stored procedure which does not work. It tries to find InSchemaName as

the variable name.

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","G5DEV" ;
CREATE PROCEDURE G5DEV.DROPDEV (
      IN INSCHEMANAME CHAR(10) )
      LANGUAGE SQL
      SPECIFIC G5DEV.DROPDEV
      NOT DETERMINISTIC
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      SET OPTION  ALWBLK = *ALLREAD ,
      ALWCPYDTA = *OPTIMIZE ,
      COMMIT = *NONE ,
      DBGVIEW = *LIST ,
      CLOSQLCSR = *ENDMOD ,
      DECRESULT = (31, 31, 00) ,
      DFTRDBCOL = G5DEV ,
      DYNDFTCOL = *NO ,
      DYNUSRPRF = *USER ,
      SRTSEQ = *HEX
      BEGIN
DROP SCHEMA INSCHEMANAME CASCADE ;
END  ;

IBM is saying I must execute the DROP statement dynamically. That doesn't
make much sense to me that I would have to do that.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.