50 - 89 of 89 tags for stored procedure

Pages

We perform ETL on a table of transactions.  After loading the transaction table, we want to use Stored Procedures (SPs) to perform the very complex business validations, and then to apply the transactions against a dozen permanent state tables.

I'm trying to parametrize a stored procedure, which takes one input string parameter and use it in the where clause in dynamic cursor.
The following snippet works with one parameter but when the list is like "US,GE", then it fails..

How to check whether the particular user has execute permission for the SP? If not how to grant?
 
Thanks in advance :)

Hi,
I have a complex Stored Procedure.
When I compile it using teradata SQL Assistant ( ODBC) it works & runs fine.
However , when I use the same piece of code and run it on Teradata Studio Express(JDBC) it doesnt.
I get the following error :

Hi,
I am converting and Oracle PL/SQL Code that supports COMMIT and ROLLBACK. I know we can use BT, ET statements to enclose the transactions. But what about if I want to do conditional Commit and rollback. Like COMMIT if A=B and RollBack if A<>B.
Example of Oracle PLSQL Code to be converted is as follows:
DECLARE

Hi folks,

We are looking at executing some dynamic SQL from some Stored Procedures.

I wanted to know what is the maximum number of characters or bytes that can be returned from a Stored Procedure, based on an "OUT" param having VARCHAR or CLOB Data Type. Has anyone already explored this...

Hi,

      Is is possible to use below insert statement in teradata

 

Insert (col1,col2,col3)

select

:Variable1 as col1,

table1.column1 as col2,

:variable2 as col3

from table1

 

(Am not having access to teradata now to test the senario. Please help me out)

 

Is it possible to declare variable in the following way in Teradata SP

 

SET Variable_name = select column_detail from table where some condition

 

Here the select will return only one value

 

Teradata Version: 12

 

Hi everyone!

I want to write SP for removing tables. The first need to find these tables (they need satisfy some conditions)  and  the second - remove it. I don't know how pass the list of tables to variables for removing theirs. And some errors occurred  during compilation.

 

Here is what I am trying to do... I am designing an extract process to export cusomters and orders.   The input will be list of customers and a date range.

The first thing it has to do is find all the orders in that data range for that group of customers.  Query on the order table.

Hi.

 

I have a problem.

I have a cursor with a select statement.

The select statement returns 5 rows.

Row 1-- Select col 1

Row 2 --,col2

Row3---,col3

Row4---,col4

Row5--- From table A;

 

Problem statement:

 

We want to set custom SQLSTATE in Teradata stored procedures. The only way to set custom SQLSTATE value is through external stored procedure.

 

Dear All,

I am trying to create an external stored procedure using JAVA but keept getting NoSuchMethodException.

I am using windows 7 to compile and create the JAR file (client)

My teradata is running on SUSE Linux(server)

My java class is:

Hi All,

Recently someone told me that using Dynamic SQL in TD procedure is not a good practice for performance reasons. Is that true? Does it mean that for every procedure call procedure needs to re-compile?

Is there any way to avoid dynamic sql completely? Parameterizing query is something which I can not avoid.

Thanks

Can I declare any variables within a trigger to store the returning value (OUT parameter, e.g. an INTEGER) from a stored procedure, like this:

CALL stdproc(variableName);

And after that, how can I use this returning value for making decisions inside the trigger (within an IF statement)?

Thanks!

More precisely: I want to use a WITH clause in a stored procedure's "SELECT ... INTO :variable FROM..." statement.

...
   DECLARE violatedRowsCount INTEGER;
	
   SELECT COUNT(*) INTO :violatedRowsCount
      FROM ( <_here_> ) AS TBL;
...

Is it possible? If yes, how?

Thanks!

Hi All,

I am writing SP to generate dynamic SQL query which creates insert statements.

I want for a particular column the data should be fetched from another table. I have tried sample and top but teradata doesnt allow sub queries with sample statements.

Here is the sample scenario which I am trying to achieve

Hi All,

Is there any way I can create function like strcture to keep my reusage SP code and can be called multiple times. I tried two difference Stored Procedure but is there any simple way to do instead of creating separate Procedure for the reusage Code.

We have a stored procedure which will look at a table and collect statistics on different columns and indexes based on types etc.  We use this inside of other stored procedures after loading staging tables.

For some reason I keep getting the following error:

I'm attempting to develop a stored procedure that takes a table name as a parameter, and performs column-by-column updates to whatever table is passed in.

I have the dynamic SELECT query working correctly:

SET CURSOR_SQL = 'SELECT * FROM ' || P_TABLE_NAME || ' ORDER BY rsid;';
PREPARE CURSOR_STATEMENT FROM CURSOR_SQL;
OPEN CDC_CURSOR;
/* logic */
CLOSE CDC_CURSOR;

Now I'm at the point where I need to fetch the cursor data into local variables...but I can't create table-specific local variables because I don't know what tables will be passed into this procedure.

Hello,

I have a current situation with a stored procedure and could use some guidance / advice. Currently we have a stored procedure that generates dynamic SQL which is based on customer inputs. Specifically, I'm having problems with a column being referenced in the SP that is undergoing funky range scans. I will refer to this column as ABC. Here are the 3 rangescan scenarios for ABC:

1) User enters the following: ABC = 100-105

Hi,
The diff. between Macros and stored procedure in Teradata is that
stored procedure require physical storage.

Can anyone clarify what does physical storage mean
and how a stored procedure consume physical storage?

I (geniunely) need to sleep inside a stored procedure as part of a loop which checks table contents and exits the loop based on content. Let's say for 5 minutes at a time.

I don't *think* there is such a command (even though there d*mn well should be :) )

Has anybody written any code which emaulates such a command?

I am at a client site where we are designing a system utilizing Stored Procedures. However, we have a dba here that has said that when Teradata 14 comes out they will no longer support Store Procedures. Is there anyone here who can shed a light on if there is truth to this or is there some kind of SP functionality they will no longer support.

Hello, I am looking to write a stored procedure that when I execute, will drop tables under a certain condition. The reason for this is to drop the temporary tables that SAP leaves behind when a job fails. Purely a testing environment proc, not meant for the customer.

Is there an equivalent to user defined function (MS SQL) in teradata where I can write SQL that returns either a scaler or table which I can further use in a query.

For example:

SELECT sclfunctionName(param1, param2)

OR

SELECT * from tblfunctionName(param1, param2)

Both sclfunctionName and tblfunctionName contain some SQL.

I have tried it using a MACRO but don't know how I can use it in SELECT

CREATE MACRO tstMacro (param1 VARCHAR(10))
AS
(
SELECT fld1, fld2, fldm FROM tbl WHERE fldm = :param1;
-- my query is really long just a small one for example
);

like

SET dyn_sql =
'Select TableName into hTableName from ' || TRIM(i_FullWorkTableName) ||
';'
;
call dbc. SysExecSQL(:dyn_sql)
;

SET dyn_sql =
'call sysdba.spETL_Table_' || hTableName|| '(:i_FullStageTableName);'
;
call dbc. SysExecSQL(:dyn_sql)
;

I get this when above is called. compiles correctly.

5568 SQL statement is not supported within a stored procedure.

What I am trying to do is call a stored procedure to work on specific table. I can get table name as an input parameter or sel from a table.
call spetl_a
call spetl_b
call spetl_c

I want to get a recordset in asp which is returned from teradata stored procedure.

Create PROCEDURE test() DYNAMIC RESULT SETS 1
BEGIN DECLARE cur1 CURSOR WITH RETURN only FOR
SELECT
col1,col2
from myTable;
OPEN cur1;
END;
-----------------it running well and get a result list correctly by execute "call test()" in SQL Assistant.----------------------------

Then I tried several different ways in ASP to get the result list:

***DB connection using ODBC as:

Set DBConn = Server.CreateObject("ADODB.Connection")

I connect to Teradata using ODBC and I would like to create very simple stored procedure. When I call SQLPrepare I receive error "[Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: Invalid SQL Statement.", but when I call the same CREATE PROCEDURE... statement using SQLExecDirect then it works.

In my opinion stored procedures can be used to enforce standards for commonly used processes and to avoid the same code to be copied out into multiple scripts/programs creating a maintenance nightmare.

But let's list some of the pro's & con's associated with stored procedures.

How do we create a procedure that has select on a table that is created in the same stored procedure.
Ex:

CREATE PROCEDURE TEST_PROC()
BEGIN

DECLARE l_count INTEGER;

CALL SomeUsr.CREATE_TEMP_TABLE();-- create a temp table in user space
SELECT COUNT(*) INTO l_count FROM SomeUsr.TEMP_TABLE;

END

Macros dont use perm space
Stored procedure requires perm space
Why?

I am new to stored procedure. We might need to capture error code from store procedure in bteq to take decision based on success/failure of stored procedure.How can I return ERRORCODE from store procedure? I would like to use BTEQ syntax .IF ERRORCODE <> 0 to take decision based on stored procedure success/failure.

We have some code being developed, which is pretty basic.
It works fine as straight SQL but gives unexpected results when the code is put in a stored procedure.
It seems that the stored procedure do NOT work when more than one punctuation character is contained in the search string.
One character in the string works as expected. More than on character does not.

The following proc doesn’t returned the expect results.
call CM_ADW_WEB_SRVC_V_DD.Account_Prospect_By_Broker_P('T.I.S. LTD.');

Last time we introduced the SQL Stored Procedure as a means to provide for Embedded Analytics.

However, as of Teradata 12.0 it is possible to use the Java Language as the basis for External Stored Procedures (known as JXSP’s), so this week we will develop a Java based version of the ApplyRiskFactorsToQuote Stored Procedure.

TD12+

Last time we introduced the Macro and the Stored Procedure as a means to provide for Isolation between the SQL call and the underlying database structure.

This week we are going to keep on the core Teradata trail by looking into Stored Procedures as means to provide for Embedded Processing.

Until you have Teradata V13 Statistics Wizard Easy Feature available for your Teradata installation, here is a set of stored procedures that will generate and/or run collect statistics statements based on a set of index, column and referential integrity rules.