Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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	Next>	Last>>