title: Teradata BTEQ : IF conditon , Validate SP compilation with runtime actual parameters & export DDL
body: <p>BTEQ related questions - I am compiling a list of SP's via BTEQ like this </p>
  
  <pre><code>.compile file=sp1 ; 
  .IF ERRORCODE &lt;&gt; 0 THEN .GOTO SQLERROR;
   compile file=sp2 ; 
  .IF ERRORCODE &lt;&gt; 0 THEN .GOTO SQLERROR;
  .logoff
   .quit
  
  .LABEL  SQLERROR
  
    .logoff ;
  
    .quit ;
  </code></pre>
  
  <p>I have  the following Q's regarding these </p>
  
  <ul>
  <li><p>If The SP compilation fails,  the following <code>.IF</code> condition, should catch it and  rest of the file should be ignored as per what the <code>.LABEL</code> , <code>SQLERROR</code> says
  <br> But that does not happen</p>
  
  <p><pre>
  .compile file=sp1 ;
  One Error/Warning.
  Warning: 5526 Stored Procedure is not created/replaced due to error(s).
  Total elapsed time was 1 second.
  Errors reported during compilation
  `---------------------------------------------------------------------------
  SPL1076:E(L3), The right parenthesis in parameter declaration is missing.</p>
  
  <p>+---------+---------+---------+---------+---------+---------+---------+----</p>
  
  <p>.IF ERRORCODE &lt;> 0 THEN .GOTO SQLERROR;
  +---------+---------+---------+---------+---------+---------+---------+----
  .compile file=sp2 ;</p>
  
  <p>Procedure has been replaced.
    Total elapsed time was 1 second.</p></li>
  </ul>
  
  <p></pre></p>
  
  <p>Why is the <code>IF</code> not working out ? <br></p>
  
  <ul>
  <li><p>The stored procedure replaces a view. The entire <code>replace stored proc</code> statement is generated via <code>show</code> that is exported . I will come to that later. Inside the SP the code is something like this 
  <pre>
  REPLACE PROCEDURE "DB"."TB"
  (IN L_N2 VARCHAR(500),
  IN L_N1 VARCHAR(500)
  )
  BEGIN</p>
  
  <p>DECLARE returncode INTEGER DEFAULT 0;
  DECLARE mystat VARCHAR(60000);</p>
  
  <p>DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
  SET RETURNCODE=1/0;
  END
  SET mystat='REPLACE VIEW '||L_N2||'.TBname ( VCol1,Vcol2) 
  As sel   col1,col2
  From '||L_N1||'.Tbname';
  CALL  dbc.sysexecsql(mystat);</p>
  
  <p>END</p></li>
  </ul>
  
  <p></pre><br> Here even if  Col1, Col2 have an issue e.g. instead of Col1 I'd put Co l1  the SP will compile . Understandably the run-time outcome of the Stored Procedure Call will not be evaluated when its compiled. This leaves a situation where any kind of error in the generated <code>replace view</code> DDL cannot be detected. Is there any way I can get the <code>replace view</code> to validate at the time of SP compilation ? <br></p>
  
  <ul>
  <li>Lastly- regards exporting DDL via  BTEQ <br>
  there is an extra space between <code>DB.TB</code> like <code>"db". "tb"</code> now when I do the same show in SQL assistant - all is well- there's no space. I have to write another sed to del the extra space .
  <br>The view and SP code  is very long and often on UNIX the code gets split across new lines like </li>
  </ul>
  
  <pre>
      sel foo_
          bar, foobar,Col.
          tb from db.tb 
      foo_bar is split across multiple lines .
  # std BTEQ options are used. The \n is because I am echoing  all this to file
  .SET ECHOREQ OFF \n
  .set width 500
  .set titledashes off \n
  .set format off \n
  .set rtitle '' \n
  .export report file="$ph" \n
   show procedure $db.$tb ; \n
  .export  reset ;  \n
  </pre>