Stored Procedure

Database
Teradata Employee

Stored Procedure

Hi,

I need to write a stored procedure for creating a user on the basis of the values in a temp table. The temp table will have only username column and based on the values in the table. My SP shud do the following

- Create role for user
- Create default database for user
- Create user
- Moving perm space to user from d_spacereserve
- Granting privs for user
- Granting role to the user

Can anyone help me please as I have never worked on SP. If you can provide me a general structure with a simple example i wud be great.

Thanks
1 REPLY
Teradata Employee

Re: Stored Procedure

Hello,

Following is the most simple example of a Stored-Procedure:

CREATE PROCEDURE spName(IN P1 INTEGER, OUT P2 INTEGER)
BEGIN
SELECT P1 + 10 INTO P2;
END;

Perhaps the task you wish to achieve can be better done using dynamic-sql or offcourse BTEQ script will be great. Following is the example of dynamic-sql in Stored-Procedure (you will have to add ID column against each username in temporary table):

CREATE PROCEDURE spName(IN P1 INTEGER, OUT P2 INTEGER)
BEGIN
DECLARE DSQL VARCHAR(100);
DELCARE UserCount INTEGER;

SELECT COUNT(*) INTO UserCount FROM VolatileTable1;

L1: LOOP
SELECT 'CREATE USER ' || UserNameColumn INTO DSQL FROM VolatileTable1 WHERE ID = UserCount;

CALL DBC.SysExecSQL(DSQL);

SELECT UserCount - 1 INTO UserCount;

IF(UserCount < 1)
LEAVE L1;
END IF;
END LOOP L1;
END;

HTH.

Regards,

Adeel