|
|
|
Junior Member
 
Group: Forum Members
Last Login: 7/25/2008 2:08:02 AM
Posts: 13,
Visits: 22
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
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
|
|
|
|