Archives of the TeradataForum

Message Posted: Wed, 03 Apr 2013 @ 12:25:11 GMT

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

<-- Anonymously Posted: Wednesday, April 03, 2013 05:40 -->

Hi All,

I know I might be a bit dumb to ask this but I?m struggling to find a way to pass the input param as a string value within quotes to where clause inside a procedure. Is there a way to do that?

My procedure goes like below. I?m attempting to generate a SQL statement and then execute it. I have masked some amount of code in the part. The snippet 1 performs as expected. In the snippet 2 part, I would like the values to be passed as value between quotes.

I tried various things options to append single quotes including the usual || and single quote method, assigning to a different variable, || 'escape'ing etc. But I run into an error everytime ?

Request you to please advise as to how this could be achieved

     REPLACE PROCEDURE UD_GEM_AD.SP_PI_ADVICE

     (
     IN DbName VARCHAR(30),
     IN TempTableName VARCHAR(30),
     IN ColumnName VARCHAR(150)
     )
     EDW_PROCESSING:

     BEGIN

     DECLARE SQLSTMNT VARCHAR(10000);

     SET SQLSTMNT = ' INS INTO
     

     sel hashamp(hashbucket(hashrow(' || TRIM(ColumnName) || ' ))) AMPNUM,
     count(*) ROWCNT FROM? '
     || TRIM(DbName)|| '.' || TRIM(TempTableName) || ' group by 1 ) AA
     /*snippet 1*/

     

     SELECT DatabaseName? AS DB, TableName? AS TB FROM DBC.tables Where tablename =
     '||TRIM(TempTableName)|| ' AND databasename=
     '||TRIM(DbName) || ' ) BB? /*snippet 2*/

     ) TEMP ' ;

     CALL DBC.SysExecSQL(:SQLSTMNT);


     END EDW_PROCESSING;

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