All Forums Database
SurK 4 posts Joined 09/12
26 Sep 2012
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.

dnoeth 4628 posts Joined 11/04
27 Sep 2012

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

Dieter

vratnam 6 posts Joined 09/12
27 Sep 2012

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

SurK 4 posts Joined 09/12
28 Sep 2012

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.
 
 
 

dnoeth 4628 posts Joined 11/04
28 Sep 2012

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

Dieter

You must sign in to leave a comment.