Help require on TD Store Procedure

Database
Fan

Help require on TD Store Procedure

Hii, 

I want to automate User Management task using Teradata Store Procedure and for that I have created one SP which will perform error checks like username length, presence of user in database before a user defined macro will insert new user record into user maintenance table.

I have used Cursor in this Procedure which will do row by row processing but I feel that this row by row processing can hamper performance because this process will be schedule to run very frequently.

Does anyone aware of any other alternative apporoach which can be use instead of Cursors? And how can I use it in inside the SP?

Thanks.

4 REPLIES
Junior Contributor

Re: Help require on TD Store Procedure

The alternative way is using plain SQL.

You didn't show any source code or other details, but presence/absence is usually checked with a simple [NOT] EXISTS subquery in SQL.

Dieter

Enthusiast

Re: Help require on TD Store Procedure

how to write bteq script in PROCEDURE plz send me the example script

thq

ratnam.

Fan

Re: Help require on TD Store Procedure

Hi Dieter,

Thanks for reply.

Here is the code of TD Store Procedure.

I would be glad if you provide some suggestions over here. :)

*********************************************************************

REPLACE PROCEDURE USERADMIN.User_Maintenance_Old

(U_Name varchar(30),U_Type varchar(20), PrjName varchar(20), Out abc char (50))

 

BEGIN

DECLARE V_BaseUsrNm varchar(30);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

FOR for_loop AS c_cursor CURSOR FOR

SELECT Username

FROM USERADMIN.UsersView

DO

SET V_BaseUsrNm = for_loop.Username;

 

/* Check existence of input User Name */

IF V_BaseUsrNm = U_Name

THEN

Set abc = 'User already exists in database';

END IF;

 

/* Check length of input User Name */

IF CHAR(SUBSTRING(U_NAME FROM 0)) > 30

THEN

Set abc = 'UserName Length Exceeded';

END IF;

 

/* Check if username meets expected criteria */

IF V_BaseUsrNm <> U_Name AND CHAR(SUBSTRING(U_NAME FROM 0)) <= 30

THEN

Set abc = 'User can be created';

INSERT INTO USERADMIN.UsersDetails(UserName,UserType,ProjectName,CreationDate,DeletionDate)

VALUES (U_Name, U_Type, PrjName, NULL, NULL);

END IF;

END FOR;

END;

*********************************************************************

Thanks.

Junior Contributor

Re: Help require on TD Store Procedure

Did you ever try to run your SP?

It's not doing what you described:

- U_Name can never be larger than 30 chars as it's defined as VARCHAR(30)

- You don't exit the loop when there's an error

- You repeat then same insert for each row in UsersView

This simple macro will do the same, but correct:

REPLACE MACRO USERADMIN.User_maintenance 
(U_Name VARCHAR(100) ,U_Type VARCHAR(20), PrjName VARCHAR(20))
AS
(
ROLLBACK 'UserName Length Exceeded'
WHERE CHAR_LENGTH(:U_Name) > 30;
ROLLBACK 'User already exists in database'
WHERE EXISTS (SELECT * FROM USERADMIN.UsersView WHERE UserName = :U_Name);

INSERT INTO USERADMIN.UsersDetails(UserName,UserType,ProjectName,CreationDate,DeletionDate)
VALUES (:U_Name, :U_Type, :PrjName, NULL, NULL);
;
);

Dieter