stored procedure

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

stored procedure

I am developing a stored procedure to perform a update on the table selecting a count from other table.

 

My inputs to the table would be Databasename,tablename and columnname

 

SyntaxEditor Code Snippet

CREATE PROCEDURE AUDT_BFR( IN DATABASENAME VARCHAR(20), IN TABLENAME VARCHAR(20), IN JOIN_CONDITION VARCHAR(20), IN COLUMNNAME VARCHAR(20))BEGIN
UPDATE AUDT_TABLE SET BFR_CNT = (SEL COUNT(*) AS COUNTS FROM :DATABSENAME.:TABLENAME A INNER JOIN MST_TBL B 
   ON A.:JOIN_CONDITION = B.:JOIN_CONDITION
   WHERE  A.:COLUMNNAME IN ('RR','RRE') AND FILE_IND = 'N')
   WHERE DATABASENAME = :DATABASENAME AND TABLENAME = :TABLENAME AND COLUMNNAME = :COLUMNNAME;
END;

 

I am getting an error on places where i give alias name (.) input variable eg. 

A.:JOIN_CONDITION

 would you help me in an option to overcome this

7 REPLIES
Junior Contributor

Re: stored procedure

To use dynamic object names you need Dynamic SQL, i.e. EXECUTE IMMEDIATE.
Enthusiast

Re: stored procedure

ok thankyou, in that case is this the correct way to write my SP ?

 

SyntaxEditor Code Snippet

CREATE PROCEDURE AUDT_BFR( DATABASENAME VARCHAR(20), TABLENAME VARCHAR(20), JOIN_CONDITION VARCHAR(20), COLUMNNAME VARCHAR(20))BEGIN
   DECLARE SQLSTR VARCHAR(500);
   SET SQLSTR = 'UPDATE AUDT_TABLE SET BFR_CNT = (SEL COUNT(*) AS COUNTS FROM '||DATABASENAME||'.'||TABLENAME ||'A INNER JOIN MST_TBL B ON A'||
   '.'||JOIN_CONDITION ||'= B'||'.'||JOIN_CONDITION ||'WHERE  A'||'.'||COLUMNNAME ||'= RR AND FILE_IND = N)'|| 
   'WHERE DATABASENAME ='||DATABASENAME ||
   'AND TABLENAME ='||TABLENAME ||'AND COLUMNNAME = '||COLUMNNAME;
END;             

 

 

Teradata Employee

Re: stored procedure

You will need to add the following statement to execute the SQL after you created your dynamic SQL in string. Make sure you have the execution right for the call.

CALL DBC.SysExecSQL(SQLSTR);

 

 Also, it's always better to add a TRIM function in the input variables when you are combining the strings.

Enthusiast

Re: stored procedure

sure, thankyou.. i will add the trim function

 

here is the updated query, i missed to have the execute immediate command before

SyntaxEditor Code Snippet

CREATE PROCEDURE AUDT_BFR( DATABASENAME VARCHAR(20), TABLENAME VARCHAR(20), JOIN_CONDITION VARCHAR(20), COLUMNNAME VARCHAR(20))BEGIN
   DECLARE SQLSTR VARCHAR(500);
   SET SQLSTR = 'UPDATE AUDT_TABLE SET BFR_CNT = (SEL COUNT(*) AS COUNTS FROM '||DATABASENAME||'.'||TABLENAME ||'A INNER JOIN MST_TBL B ON A'||
   '.'||JOIN_CONDITION ||'= B'||'.'||JOIN_CONDITION ||'WHERE  A'||'.'||COLUMNNAME ||'= RR AND FILE_IND = N)'|| 
   'WHERE DATABASENAME ='||DATABASENAME ||
   'AND TABLENAME ='||TABLENAME ||'AND COLUMNNAME = '||COLUMNNAME;
   EXECUTE IMMEDIATE SQLSTR ;
END;             

 

I have asked access to create proc for my id to the dba as i get this error

 

 

SPL1040:E(L3), Invalid definition for stored procedure 'E(3524):The user does not have CREATE PROCEDURE access to database '. 

 

 

 

 

 

Teradata Employee

Re: stored procedure

Look like your user account does not have "Create Procedure" permission on the target database, you have to ask your DBA to grant that permission. Also, you may want to double check your query. You may need to add a space somewhere like ' WHERE DATABASENAME ='||DATABASENAME ||

 

Enthusiast

Re: stored procedure

Thank you.. is there a way to display the output of the stored proc without executing the output query

Teradata Employee

Re: stored procedure

Your dynamic SQL is just VARCHAR string, you can either add a return column for the dynamic SQL string or you can add a step to insert it into a empty table for debugging purpose.


parthismail wrote:

Thank you.. is there a way to display the output of the stored proc without executing the output query