Stored procedures

Database
Enthusiast

Stored procedures

Currently I have a SQL statement that will generate an answer set of INSERT statements.

I want to write a stored procedure that will first run my initial SQL with a variable for databasename, after inputting the databasename it will perform SQL that will generate the SQL for the insert statements (lots of them).

I then want it to run all of those individual INSERT statements so they load into a hard coded table.

Any help would be great.

10 REPLIES
Junior Contributor

Re: Stored procedures

Of course it's possible within an SP, but "lots of INSERT statements" are probably the wrong way to handle it.

Are there multiple source/target tables for the INSERTs?

Could you share your existing logic to create those INSERTs?

Dieter

Junior Contributor

Re: Stored procedures

Ok, it's different source tables for each INSERT, so there's no way to optimize the number of INSERTs.

But you should change some syntax:

The row count should be calculated without DatabaseName etc:

(SELECT CAST(COUNT(*) AS DECIMAL(18,0))

The same for the count per value, the outer most select should add 'DatabaseName, TableName, ColumnName'

Finally the percentage calculation should be simplified to:

TRIM(100 * Y/X)

Then it's just a CURSOR using this SELECT and a "CALL DBC.SysExecSQL(:variable_containing_Insert_statement);" for each row.

Btw, if you don't need fully detailed information info about row count and the number of rows for the most common values can also be extracted using HELP STATS when stats have been collected

Dieter

Junior Contributor

Re: Stored procedures

Check the FOR syntax and the rules for dynamic SQL.

Depending on your TD release you can use dbc.SysExecSQL or EXECUTE IMMEDIATE to dynamically submit the Inserts.

FOR cur AS 
SELECT 'INSERT INTO UDWETLSANDBOX.PRFL_COL_DOMAIN '||
'SELECT '||
...
'GROUP BY 1,2,3,4) AS X (XDB,XTB,XCL,XCDV,XNV,XPERV,XTS);' AS str
FROM DBC.COLUMNS A
WHERE A.DATABASENAME = 'UDW1ETLSRCREP'
AND A.COLUMNNAME LIKE '%CD%'
DO
CALL dbc.SysExecSQL(cur.str) ;
END FOR;

Dieter

Junior Contributor

Re: Stored procedures

This is a SP to collect row counts from all tables within a database, it's very basic, no error checking etc.

But it shows a cursor and dynamic SQL using dbc.SysExecSQL or EXECUTE IMMEDIATE:

CREATE SET TABLE RowCounts

     (

      DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      RowCount DECIMAL(18,0),

      COllectTimeStamp TIMESTAMP(0))

PRIMARY INDEX ( DatabaseName ,TableName )

;

REPLACE PROCEDURE GetRowCounts(IN DBName VARCHAR(30))

BEGIN

  DECLARE SqlTxt VARCHAR(500);

   FOR cur AS

      SELECT

         TRIM(DatabaseName) AS DBName,

         TRIM(TableName) AS TabName

      FROM dbc.Tables

      WHERE DatabaseName = :DBName

      AND TableKind = 'T'

   DO

     SET SqlTxt =

         'INSERT INTO RowCounts ' ||

         'SELECT ' ||

         '''' || cur.DBName || '''' || ',' ||

         '''' || cur.TabName || '''' || ',' ||

         'count(*)' ||  ',' ||

         'CURRENT_TIMESTAMP(0) ' ||

         'FROM ' || cur.DBName ||

         '.' || cur.TabName || ';';

     CALL dbc.sysexecsql(:SqlTxt);

     -- EXECUTE IMMEDIATE sqlTxt;

   END FOR;

END;

Dieter

Enthusiast

Re: Stored procedures

Thanks again! I tried running this after configuring it to line up with what I am doing but I am having an issue actually calling the procedure once its created.

I limited because of a SELECT WITH GRANT OPTION access problem, is this because the database I made the SP in does not have access, or because personally I do not have access?

Enthusiast

Re: Stored procedures

Resolved my issues thanks so much!

Fan

Re: Stored procedures

Hai I am new for teradata can any one tell me what it mean by indicators dashboard? And what is use of it?

Thank you

Enthusiast

Re: Stored procedures

Hello everyone!

I can't create stored procedure which will execute (call) other existing stored procedures.

Can somebody help me?

Re: Stored procedures

You might be getting the error,

'' An owner referenced by the user does not have execute permissions on the store procedure xyz ''.

To resolve this, you must have the execute grant access on the inner store procedure(xyz) to execute it in another procedure.

Administrator can grant the access.

(Additional Info : You may also need to check the SQL SECURITY OWNER/CREATOR info on both the store procedures. )

- Vijay Karumudi