All Forums Database
idg_tddevex 18 posts Joined 10/06
04 Jun 2008
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

Adeel Chaudhry 773 posts Joined 04/08
04 Jun 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.