Exec Sql and using a variable

[DeadManWalks]

Hi,

So I can do this;

       Monitor;
       exec sql
          Drop trigger ItemAttributes_Insert;
       on-error;
       EndMon;
       
       Exec Sql
          create Trigger MM760DVP/ItemAttributes_Insert                   
          after insert on MstIta                                   
          Referencing New row as NRow                              
          for each row mode db2sql                                 
          Begin                                                    
             Declare Found Char(1);                                
             Set Found = 'N';                                      
             Select 'Y' into Found from sysibm/sysdummy1           
             where Exists                                   
                (select * from MstIta_log A                    
                 where A.inumbr = NRow.Inumbr);                 
             If Found = 'N' Then                                   
                Insert into MstIta_log                             
                Values(NRow.Inumbr,User, Current Date, DEFAULT, 
                '0001-01-01');                           
             end if;                                               
          End                                                      
       ;

But I want to do this;

 MyVariable = 'Stuf Stuf.....LIBRARYNAME....Stuf..Stuf..';
  Exec Sql MyVariable;

I want to not hard code the library.

[DeadManWalks]

Re: Exec Sql and using a variable

I did it a better way. I did a chgcurlib to the libs that I want.

I would like to know if you can do this with building a string and firing it off.

[B.Hauser]

Re: Exec Sql and using a variable

If you do not want to hardcode a library/schema, you need to use dynamic SQL.
A create Trigger or a create procedure statement can be executed dynamically.
You need to build the string containing the complete SQL statement including the library as string.
The Strign can be executed with the EXECUTE IMMEDIATE Statement:

  String = 'Create Trigger ....';
  Exec SQL   Execute immeditate :String;
  If SQLCODE < *Zeros;
     //Error
  EndIf;

Birgitta

[DeadManWalks]

Re: Exec Sql and using a variable

Thank you very much Birgitta.