CODEWITHSUNDEEP
db2ibm-midrange
RockBoro
RockBoro

Reputation: 2473

how to create SQL procedure which contains compile time errors?

I am writing a one time SQL procedure which will add an identity column to a table, then set the primary key of the table to that new identity column. But I cannot compile the SQL procedure because the CREATE PROCEDURE statement returns a compile error.

How to tell SQL to create the procedure despite the compile error?

CREATE or replace PROCEDURE TESTSQL(                                
 )                                                                  
LANGUAGE    SQL                                                     
SPECIFIC        ORDHEADP5T                                          
SET OPTION   DATFMT    = *ISO, DLYPRP = *YES, DBGVIEW = *SOURCE,    
             USRPRF    = *OWNER, DYNUSRPRF = *OWNER, COMMIT = *CHG  
BEGIN                                                               
                                                                    
alter table   ordheader                                             
add column   ordidnum int generated always as identity ;            
                                                                    
alter table     ordheader                                           
add constraint  pk_ordheader primary key ( ordidnum ) ;             
                                                                    
end                                                                 

SQL0205  30      14  Position 44 Column ORDIDNUM not in table ORDHEADER

Upvotes: 0

Views: 125

Answers (1)

nfgl
nfgl

Reputation: 3212

You can do this one if you reduce it to one alter table like :

CREATE or replace PROCEDURE TESTSQL()                                                                  
LANGUAGE    SQL                                                     
SPECIFIC        ORDHEADP5T                                          
SET OPTION   DATFMT    = *ISO, DLYPRP = *YES, DBGVIEW = *SOURCE,    
             USRPRF    = *OWNER, DYNUSRPRF = *OWNER, COMMIT = *CHG  
BEGIN                                                               
alter table   ordheader                                             
    add column   ordidnum int generated always as identity          
    add constraint  pk_ordheader primary key ( ordidnum ) ;             
end

Upvotes: 4

Related Questions