Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 18 Jan 2013 @ 19:43:46 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Handling IN clause in stored procedure
From:   Geoffrey Rommel

This question comes up often, and you can probably find some suggestions by searching the Forum archive. The basic concept is that a variable in a macro or SP -- e.g. :empid -- can only appear where a literal value would appear. For instance, where empid = '123' is okay, so where empid = :empid will also work. However, where empid in ('123','456') contains two literals, so you would have to say where empid in (:empid1, :empid2).

The most elegant way around this is to construct a dynamic SQL statement and then execute it, like so:

     declare upd_stmt? varchar(2000);

     upd_stmt = 'update employee set deptname= '''|| deptname ||'''
     where empid in ('|| empid_list || ');' ;

     call dbc.SysExecSQL(:upd_stmt);

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020